import sqlite3
import datetime as dt
from typing import List, Optional, Dict, Any
from ..database import _conn

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

def get_all_courses(status: Optional[str] = None, user_id: Optional[int] = None) -> List[sqlite3.Row]:
    """Get all courses, optionally filtered by status"""
    with _conn() as conn:
        if status:
            rows = conn.execute(
                """
                SELECT c.*, u.name as created_by_name
                FROM academy_courses c
                LEFT JOIN users u ON u.id = c.created_by
                WHERE c.status = ?
                ORDER BY c.order_index ASC, c.created_at DESC
                """,
                (status,)
            ).fetchall()
        else:
            rows = conn.execute(
                """
                SELECT c.*, u.name as created_by_name
                FROM academy_courses c
                LEFT JOIN users u ON u.id = c.created_by
                ORDER BY c.order_index ASC, c.created_at DESC
                """
            ).fetchall()
    return rows

def get_course_by_id(course_id: int) -> Optional[sqlite3.Row]:
    """Get a single course by ID"""
    with _conn() as conn:
        row = conn.execute(
            """
            SELECT c.*, u.name as created_by_name
            FROM academy_courses c
            LEFT JOIN users u ON u.id = c.created_by
            WHERE c.id = ?
            """,
            (course_id,)
        ).fetchone()
    return row

def create_course(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"""
    now = dt.datetime.now().strftime('%Y-%m-%dT%H:%M:%S')
    with _conn() as conn:
        cur = conn.execute(
            """
            INSERT INTO academy_courses (title, description, thumbnail, status, created_by, created_at, updated_at, order_index)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            """,
            (title, description, thumbnail, status, created_by, now, now, order_index)
        )
        conn.commit()
        return cur.lastrowid

def update_course(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'] = dt.datetime.now().strftime('%Y-%m-%dT%H:%M:%S')
    
    set_clause = ', '.join(f"{k} = ?" for k in updates.keys())
    values = list(updates.values()) + [course_id]
    
    with _conn() as conn:
        conn.execute(
            f"UPDATE academy_courses SET {set_clause} WHERE id = ?",
            values
        )
        conn.commit()
    return True

def delete_course(course_id: int) -> bool:
    """Delete a course and its lessons"""
    with _conn() as conn:
        conn.execute("DELETE FROM academy_courses WHERE id = ?", (course_id,))
        conn.commit()
    return True

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

def get_sections_by_course(course_id: int) -> List[sqlite3.Row]:
    """Get all sections for a course"""
    with _conn() as conn:
        rows = conn.execute(
            """
            SELECT * FROM academy_sections
            WHERE course_id = ?
            ORDER BY order_index ASC, created_at ASC
            """,
            (course_id,)
        ).fetchall()
    return rows

def get_section_by_id(section_id: int) -> Optional[sqlite3.Row]:
    """Get a single section by ID"""
    with _conn() as conn:
        row = conn.execute(
            "SELECT * FROM academy_sections WHERE id = ?",
            (section_id,)
        ).fetchone()
    return row

def create_section(course_id: int, title: str, description: Optional[str] = None,
                  order_index: int = 0) -> int:
    """Create a new section"""
    now = dt.datetime.now().strftime('%Y-%m-%dT%H:%M:%S')
    with _conn() as conn:
        cur = conn.execute(
            """
            INSERT INTO academy_sections (course_id, title, description, order_index, created_at, updated_at)
            VALUES (?, ?, ?, ?, ?, ?)
            """,
            (course_id, title, description, order_index, now, now)
        )
        conn.commit()
        return cur.lastrowid

def update_section(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'] = dt.datetime.now().strftime('%Y-%m-%dT%H:%M:%S')
    
    set_clause = ', '.join(f"{k} = ?" for k in updates.keys())
    values = list(updates.values()) + [section_id]
    
    with _conn() as conn:
        conn.execute(
            f"UPDATE academy_sections SET {set_clause} WHERE id = ?",
            values
        )
        conn.commit()
    return True

def delete_section(section_id: int) -> bool:
    """Delete a section (lessons will have section_id set to NULL)"""
    with _conn() as conn:
        conn.execute("DELETE FROM academy_sections WHERE id = ?", (section_id,))
        conn.commit()
    return True

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

def get_lessons_by_course(course_id: int, status: Optional[str] = None) -> List[sqlite3.Row]:
    """Get all lessons for a course"""
    with _conn() as conn:
        if status:
            rows = conn.execute(
                """
                SELECT * FROM academy_lessons
                WHERE course_id = ? AND status = ?
                ORDER BY order_index ASC, created_at ASC
                """,
                (course_id, status)
            ).fetchall()
        else:
            rows = conn.execute(
                """
                SELECT * FROM academy_lessons
                WHERE course_id = ?
                ORDER BY order_index ASC, created_at ASC
                """,
                (course_id,)
            ).fetchall()
    return rows

def get_lesson_by_id(lesson_id: int) -> Optional[sqlite3.Row]:
    """Get a single lesson by ID"""
    with _conn() as conn:
        row = conn.execute(
            "SELECT * FROM academy_lessons WHERE id = ?",
            (lesson_id,)
        ).fetchone()
    return row

def create_lesson(course_id: int, title: str, content_type: str, content_data: str,
                 description: Optional[str] = None, duration_minutes: Optional[int] = None,
                 order_index: int = 0, status: str = 'published', section_id: Optional[int] = None,
                 attachment_url: Optional[str] = None, attachment_name: Optional[str] = None) -> int:
    """Create a new lesson"""
    now = dt.datetime.now().strftime('%Y-%m-%dT%H:%M:%S')
    with _conn() as conn:
        cur = conn.execute(
            """
            INSERT INTO academy_lessons (course_id, section_id, title, description, content_type, content_data, 
                                        duration_minutes, order_index, status, attachment_url, attachment_name, created_at, updated_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """,
            (course_id, section_id, title, description, content_type, content_data, duration_minutes, 
             order_index, status, attachment_url, attachment_name, now, now)
        )
        conn.commit()
        return cur.lastrowid

def update_lesson(lesson_id: int, **kwargs) -> bool:
    """Update a lesson"""
    allowed_fields = ['section_id', 'title', 'description', 'content_type', 'content_data', 
                     'duration_minutes', 'order_index', 'status', 'attachment_url', 'attachment_name']
    updates = {k: v for k, v in kwargs.items() if k in allowed_fields}
    
    if not updates:
        return False
    
    updates['updated_at'] = dt.datetime.now().strftime('%Y-%m-%dT%H:%M:%S')
    
    set_clause = ', '.join(f"{k} = ?" for k in updates.keys())
    values = list(updates.values()) + [lesson_id]
    
    with _conn() as conn:
        conn.execute(
            f"UPDATE academy_lessons SET {set_clause} WHERE id = ?",
            values
        )
        conn.commit()
    return True

def delete_lesson(lesson_id: int) -> bool:
    """Delete a lesson"""
    try:
        with _conn() as conn:
            # First check if lesson exists
            cursor = conn.execute("SELECT id FROM academy_lessons WHERE id = ?", (lesson_id,))
            if not cursor.fetchone():
                raise Exception(f"Lección con ID {lesson_id} no encontrada")
            
            # Delete the lesson
            conn.execute("DELETE FROM academy_lessons WHERE id = ?", (lesson_id,))
            conn.commit()
            return True
    except Exception as e:
        print(f"Database error deleting lesson {lesson_id}: {e}")
        raise e

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

def enroll_user(user_id: int, course_id: int) -> int:
    """Enroll a user in a course"""
    now = dt.datetime.now().strftime('%Y-%m-%dT%H:%M:%S')
    with _conn() as conn:
        try:
            cur = conn.execute(
                """
                INSERT INTO academy_enrollments (user_id, course_id, enrolled_at)
                VALUES (?, ?, ?)
                """,
                (user_id, course_id, now)
            )
            conn.commit()
            return cur.lastrowid
        except sqlite3.IntegrityError:
            # Already enrolled
            row = conn.execute(
                "SELECT id FROM academy_enrollments WHERE user_id = ? AND course_id = ?",
                (user_id, course_id)
            ).fetchone()
            return row['id'] if row else 0

def get_user_enrollments(user_id: int) -> List[sqlite3.Row]:
    """Get all courses a user is enrolled in"""
    with _conn() as conn:
        rows = conn.execute(
            """
            SELECT e.*, c.title, c.description, c.thumbnail, c.status
            FROM academy_enrollments e
            JOIN academy_courses c ON c.id = e.course_id
            WHERE e.user_id = ?
            ORDER BY e.enrolled_at DESC
            """,
            (user_id,)
        ).fetchall()
    return rows

def is_user_enrolled(user_id: int, course_id: int) -> bool:
    """Check if user is enrolled in a course"""
    with _conn() as conn:
        row = conn.execute(
            "SELECT id FROM academy_enrollments WHERE user_id = ? AND course_id = ?",
            (user_id, course_id)
        ).fetchone()
    return row is not None

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

def update_progress(user_id: int, lesson_id: int, completed: bool) -> int:
    """Update user's progress on a lesson"""
    now = dt.datetime.now().strftime('%Y-%m-%dT%H:%M:%S')
    completed_at = now if completed else None
    
    with _conn() as conn:
        # Try to update existing record
        result = conn.execute(
            """
            UPDATE academy_progress 
            SET completed = ?, completed_at = ?, last_accessed_at = ?
            WHERE user_id = ? AND lesson_id = ?
            """,
            (int(completed), completed_at, now, user_id, lesson_id)
        )
        
        if result.rowcount == 0:
            # Insert new record
            cur = conn.execute(
                """
                INSERT INTO academy_progress (user_id, lesson_id, completed, completed_at, last_accessed_at)
                VALUES (?, ?, ?, ?, ?)
                """,
                (user_id, lesson_id, int(completed), completed_at, now)
            )
            conn.commit()
            return cur.lastrowid
        else:
            conn.commit()
            # Get the ID
            row = conn.execute(
                "SELECT id FROM academy_progress WHERE user_id = ? AND lesson_id = ?",
                (user_id, lesson_id)
            ).fetchone()
            return row['id'] if row else 0

def get_user_progress(user_id: int, course_id: Optional[int] = None) -> List[sqlite3.Row]:
    """Get user's progress, optionally filtered by course"""
    with _conn() as conn:
        if course_id:
            rows = conn.execute(
                """
                SELECT p.*, l.title as lesson_title, l.course_id
                FROM academy_progress p
                JOIN academy_lessons l ON l.id = p.lesson_id
                WHERE p.user_id = ? AND l.course_id = ?
                ORDER BY l.order_index ASC
                """,
                (user_id, course_id)
            ).fetchall()
        else:
            rows = conn.execute(
                """
                SELECT p.*, l.title as lesson_title, l.course_id
                FROM academy_progress p
                JOIN academy_lessons l ON l.id = p.lesson_id
                WHERE p.user_id = ?
                ORDER BY p.last_accessed_at DESC
                """,
                (user_id,)
            ).fetchall()
    return rows

def get_course_progress_stats(user_id: int, course_id: int) -> Dict[str, Any]:
    """Get progress stats for a course"""
    with _conn() as conn:
        # Total lessons
        total = conn.execute(
            "SELECT COUNT(*) as count FROM academy_lessons WHERE course_id = ? AND status = 'published'",
            (course_id,)
        ).fetchone()
        
        # Completed lessons
        completed = conn.execute(
            """
            SELECT COUNT(*) as count
            FROM academy_progress p
            JOIN academy_lessons l ON l.id = p.lesson_id
            WHERE p.user_id = ? AND l.course_id = ? AND p.completed = 1
            """,
            (user_id, course_id)
        ).fetchone()
    
    total_count = total['count'] if total else 0
    completed_count = completed['count'] if completed else 0
    percentage = (completed_count / total_count * 100) if total_count > 0 else 0
    
    return {
        'total': total_count,
        'completed': completed_count,
        'percentage': round(percentage, 1)
    }