from datetime import datetime, date
from sqlalchemy.orm import Session
from sqlalchemy import and_, or_, text
from ...models.models import AcademicSession, Speciality, Course, Timetable
from src.models import DatabaseContextManager
import uuid

class AcademicSessionManager:
    """Manager class for academic session operations"""
    
    def get_all_sessions(self):
        """Get all academic sessions"""
        try:
            with DatabaseContextManager() as db:
                sessions = db.session.query(AcademicSession).all()
                return [self._session_to_dict_with_context(db, session) for session in sessions]
        except Exception as e:
            print(f"Error getting all sessions: {e}")
            return []
    
    def get_sessions_by_speciality(self, speciality_id: str):
        """Get academic sessions for a specific speciality"""
        try:
            with DatabaseContextManager() as db:
                sessions = db.session.query(AcademicSession).filter(
                    AcademicSession.speciality_id == speciality_id
                ).order_by(AcademicSession.created_at.desc()).all()
            
                return [self._session_to_dict_with_context(db, session) for session in sessions]
        except Exception as e:
            print(f"Error getting sessions by speciality: {e}")
            return []
    
    def get_session_by_id(self, session_id: str):
        """Get a specific academic session by ID"""
        try:
            with DatabaseContextManager() as db:
                session = db.session.query(AcademicSession).filter(
                    AcademicSession.id == session_id
                ).first()
            
                if session:
                    return self._session_to_dict_with_context(db, session)
                return None
        except Exception as e:
            print(f"Error getting session by ID: {e}")
            return None
    
    def create_session(self, data: dict):
        """Create a new academic session"""
        try:
            with DatabaseContextManager() as db:
            # Parse dates
                start_date = datetime.strptime(data['start_date'], '%Y-%m-%d').date()
                end_date = datetime.strptime(data['end_date'], '%Y-%m-%d').date()
                
                # Validate dates
                if start_date >= end_date:
                    raise ValueError("End date must be after start date")
                
                # Check if speciality exists
                speciality = db.session.query(Speciality).filter(
                    Speciality.id == data['speciality_id']
                    ).first()
                
                if not speciality:
                    raise ValueError("Speciality not found")
                
                # Create new session
                new_session = AcademicSession(
                    id=str(uuid.uuid4()),
                    name=data['name'],
                    year=data['year'],
                    speciality_id=data['speciality_id'],
                    start_date=start_date,
                    end_date=end_date,
                    is_active=data.get('is_active', True),
                    status=data.get('status', 'active'),
                    description=data.get('description', ''),
                    created_at=datetime.utcnow(),
                    updated_at=datetime.utcnow()
                )
                
                db.session.add(new_session)
                db.session.commit()
                db.session.refresh(new_session)
                
                return self._session_to_dict_with_context(db, new_session)
        except Exception as e:
            with DatabaseContextManager() as db:
                db.session.rollback()
            print(f"Error creating session: {e}")
            raise e
    
    def update_session(self, session_id: str, data: dict):
        """Update an existing academic session"""
        try:
            with DatabaseContextManager() as db:
                session = db.session.query(AcademicSession).filter(
                    AcademicSession.id == session_id
                ).first()
            
                if not session:
                    return None
                
                # Update fields
                if 'name' in data:
                    session.name = data['name']
                if 'year' in data:
                    session.year = data['year']
                if 'start_date' in data:
                    session.start_date = datetime.strptime(data['start_date'], '%Y-%m-%d').date()
                if 'end_date' in data:
                    session.end_date = datetime.strptime(data['end_date'], '%Y-%m-%d').date()
                if 'is_active' in data:
                    session.is_active = data['is_active']
                if 'status' in data:
                    session.status = data['status']
                if 'description' in data:
                    session.description = data['description']
                
                # Validate dates if both are present
                if session.start_date and session.end_date and session.start_date >= session.end_date:
                    raise ValueError("End date must be after start date")
                
                session.updated_at = datetime.utcnow()
                
                db.session.commit()
                db.session.refresh(session)
                
                # Convert to dict within the same context
                return self._session_to_dict_with_context(db, session)
        except Exception as e:
            with DatabaseContextManager() as db:
                db.session.rollback()
            print(f"Error updating session: {e}")
            raise e
    
    def delete_session(self, session_id: str, requester_id: str):
        """Delete an academic session"""
        try:
            with DatabaseContextManager() as db:
                session = db.session.query(AcademicSession).filter(
                    AcademicSession.id == session_id
                ).first()
            
                if not session:
                    return False
                
                # Check if session has associated courses
                courses_count = db.session.query(Course).filter(
                        Course.academic_session_id == session_id
                    ).count()
                
                if courses_count > 0:
                    raise ValueError(f"Cannot delete academic session with {courses_count} associated courses. Please remove or reassign the courses first.")
                
                # Check if session has associated timetables
                timetables_count = db.session.query(Timetable).filter(
                        Timetable.academic_session_id == session_id
                    ).count()
                
                if timetables_count > 0:
                    raise ValueError(f"Cannot delete academic session with {timetables_count} associated timetables. Please remove or reassign the timetables first.")
                
                # Check if session has enrolled students
                students_count = db.session.execute(
                    text("SELECT COUNT(*) FROM student_academic_session_association WHERE academic_session_id = :session_id"),
                    {"session_id": session_id}
                ).scalar()
                
                if students_count > 0:
                    raise ValueError(f"Cannot delete academic session with {students_count} enrolled students. Please remove or reassign the students first.")
                
                # Delete the session
                db.session.delete(session)
                db.session.commit()
                
                return True
        except Exception as e:
            with DatabaseContextManager() as db:
                db.session.rollback()
            print(f"Error deleting session: {e}")
            raise e
    
    def _session_to_dict_with_context(self, db, session: AcademicSession):
        """Convert AcademicSession model to dictionary within the same database context"""
        # Get counts of associated data
        courses_count = db.session.query(Course).filter(
            Course.academic_session_id == session.id
        ).count()
    
        timetables_count = db.session.query(Timetable).filter(
            Timetable.academic_session_id == session.id
        ).count()
    
        return {
            'id': session.id,
            'name': session.name,
            'year': session.year,
            'speciality_id': session.speciality_id,
            'start_date': session.start_date.isoformat() if session.start_date else None,
            'end_date': session.end_date.isoformat() if session.end_date else None,
            'is_active': session.is_active,
            'status': session.status,
            'description': session.description,
            'created_at': session.created_at.isoformat() if session.created_at else None,
            'updated_at': session.updated_at.isoformat() if session.updated_at else None,
            'duration': self._calculate_duration(session.start_date, session.end_date),
            'courses_count': courses_count,
            'timetables_count': timetables_count
        }

    def _session_to_dict(self, session: AcademicSession):
        """Convert AcademicSession model to dictionary"""
        with DatabaseContextManager() as db:
            # Get counts of associated data
            courses_count = db.session.query(Course).filter(
                Course.academic_session_id == session.id
            ).count()
        
            timetables_count = db.session.query(Timetable).filter(
                Timetable.academic_session_id == session.id
            ).count()
        
            return {
                'id': session.id,
                'name': session.name,
                'year': session.year,
                'speciality_id': session.speciality_id,
                'start_date': session.start_date.isoformat() if session.start_date else None,
                'end_date': session.end_date.isoformat() if session.end_date else None,
                'is_active': session.is_active,
                'status': session.status,
                'description': session.description,
                'created_at': session.created_at.isoformat() if session.created_at else None,
                'updated_at': session.updated_at.isoformat() if session.updated_at else None,
                'duration': self._calculate_duration(session.start_date, session.end_date),
                'courses_count': courses_count,
                'timetables_count': timetables_count
            }
    
    def _calculate_duration(self, start_date: date, end_date: date):
        """Calculate duration in days between two dates"""
        if start_date and end_date:
            return (end_date - start_date).days
        return 0
    
    def get_active_sessions(self):
        """Get all active academic sessions"""
        try:
            with DatabaseContextManager() as db:
                sessions = db.session.query(AcademicSession).filter(
                    AcademicSession.is_active == True
                ).all()
            
                return [self._session_to_dict_with_context(db, session) for session in sessions]
        except Exception as e:
            print(f"Error getting active sessions: {e}")
            return []
    
    def get_sessions_by_status(self, status: str):
        """Get academic sessions by status"""
        try:
            with DatabaseContextManager() as db:
                sessions = db.session.query(AcademicSession).filter(
                    AcademicSession.status == status
                ).all()
            
                return [self._session_to_dict_with_context(db, session) for session in sessions]
        except Exception as e:
            print(f"Error getting sessions by status: {e}")
            return []
    
    def get_sessions_by_year(self, year: str):
        """Get academic sessions by year"""
        try:
            with DatabaseContextManager() as db:
                sessions = db.session.query(AcademicSession).filter(
                    AcademicSession.year == year
                ).all()
                
                return [self._session_to_dict_with_context(db, session) for session in sessions]
        except Exception as e:
            print(f"Error getting sessions by year: {e}")
            return []

    # Student relationship methods
    def get_students_in_session(self, session_id: str):
        """Get all students enrolled in a specific academic session"""
        try:
            with DatabaseContextManager() as db:
                # Query the association table
                student_relationships = db.session.execute("""
                    SELECT 
                        s.id, s.first_name, s.last_name, s.email, s.student_id,
                        sasa.enrollment_date, sasa.status, sasa.enrollment_type,
                        sasa.academic_standing, sasa.notes
                    FROM student_academic_session_association sasa
                    JOIN students s ON sasa.student_id = s.id
                    WHERE sasa.academic_session_id = :session_id
                    ORDER BY s.last_name, s.first_name
                """, {'session_id': session_id})
                
                students = []
                for row in student_relationships:
                    students.append({
                        'id': row[0],
                        'first_name': row[1],
                        'last_name': row[2],
                        'email': row[3],
                        'student_id': row[4],
                        'enrollment_date': row[5].isoformat() if row[5] else None,
                        'status': row[6],
                        'enrollment_type': row[7],
                        'academic_standing': row[8],
                        'notes': row[9]
                    })
                
                return students
        except Exception as e:
            print(f"Error getting students in session: {e}")
            return []

    def enroll_student_in_session(self, session_id: str, student_id: str, enrollment_data: dict):
        """Enroll a student in an academic session"""
        try:
            with DatabaseContextManager() as db:
                # Check if student is already enrolled
                existing_enrollment = db.session.execute("""
                    SELECT 1 FROM student_academic_session_association 
                    WHERE student_id = :student_id AND academic_session_id = :session_id
                """, {'student_id': student_id, 'session_id': session_id})
                
                if existing_enrollment.fetchone():
                    raise ValueError("Student is already enrolled in this session")
                
                # Insert new enrollment
                db.session.execute("""
                    INSERT INTO student_academic_session_association 
                    (student_id, academic_session_id, enrollment_date, status, enrollment_type, 
                     academic_standing, notes, created_at, updated_at)
                    VALUES (:student_id, :session_id, :enrollment_date, :status, :enrollment_type,
                           :academic_standing, :notes, :created_at, :updated_at)
                """, {
                    'student_id': student_id,
                    'session_id': session_id,
                    'enrollment_date': enrollment_data.get('enrollment_date', datetime.utcnow().date()),
                    'status': enrollment_data.get('status', 'active'),
                    'enrollment_type': enrollment_data.get('enrollment_type', 'regular'),
                    'academic_standing': enrollment_data.get('academic_standing', 'good'),
                    'notes': enrollment_data.get('notes'),
                    'created_at': datetime.utcnow(),
                    'updated_at': datetime.utcnow()
                })
                
                # Update students_count in academic_sessions table
                db.session.execute("""
                    UPDATE academic_sessions 
                    SET students_count = (
                        SELECT COUNT(*) 
                        FROM student_academic_session_association 
                        WHERE academic_session_id = :session_id AND status = 'active'
                    )
                    WHERE id = :session_id
                """, {'session_id': session_id})
                
                db.session.commit()
                return True
        except Exception as e:
            with DatabaseContextManager() as db:
                db.session.rollback()
            print(f"Error enrolling student in session: {e}")
            raise e

    def update_student_enrollment(self, session_id: str, student_id: str, update_data: dict):
        """Update a student's enrollment in an academic session"""
        try:
            with DatabaseContextManager() as db:
                # Update enrollment
                update_fields = []
                params = {'student_id': student_id, 'session_id': session_id}
                
                if 'status' in update_data:
                    update_fields.append("status = :status")
                    params['status'] = update_data['status']
                
                if 'enrollment_type' in update_data:
                    update_fields.append("enrollment_type = :enrollment_type")
                    params['enrollment_type'] = update_data['enrollment_type']
                
                if 'academic_standing' in update_data:
                    update_fields.append("academic_standing = :academic_standing")
                    params['academic_standing'] = update_data['academic_standing']
                
                if 'completion_date' in update_data:
                    update_fields.append("completion_date = :completion_date")
                    params['completion_date'] = update_data['completion_date']
                
                if 'withdrawal_date' in update_data:
                    update_fields.append("withdrawal_date = :withdrawal_date")
                    params['withdrawal_date'] = update_data['withdrawal_date']
                
                if 'withdrawal_reason' in update_data:
                    update_fields.append("withdrawal_reason = :withdrawal_reason")
                    params['withdrawal_reason'] = update_data['withdrawal_reason']
                
                if 'notes' in update_data:
                    update_fields.append("notes = :notes")
                    params['notes'] = update_data['notes']
                
                if update_fields:
                    update_fields.append("updated_at = :updated_at")
                    params['updated_at'] = datetime.utcnow()
                    
                    query = f"""
                        UPDATE student_academic_session_association 
                        SET {', '.join(update_fields)}
                        WHERE student_id = :student_id AND academic_session_id = :session_id
                    """
                    
                    db.session.execute(query, params)
                    
                    # Update students_count in academic_sessions table
                    db.session.execute("""
                        UPDATE academic_sessions 
                        SET students_count = (
                            SELECT COUNT(*) 
                            FROM student_academic_session_association 
                            WHERE academic_session_id = :session_id AND status = 'active'
                        )
                        WHERE id = :session_id
                    """, {'session_id': session_id})
                    
                    db.session.commit()
                    return True
                else:
                    return False  # No fields to update
        except Exception as e:
            with DatabaseContextManager() as db:
                db.session.rollback()
            print(f"Error updating student enrollment: {e}")
            raise e

    def withdraw_student_from_session(self, session_id: str, student_id: str, withdrawal_reason: str = None):
        """Withdraw a student from an academic session"""
        try:
            with DatabaseContextManager() as db:
                # Update enrollment status to withdrawn
                db.session.execute("""
                    UPDATE student_academic_session_association 
                    SET status = 'withdrawn', 
                        withdrawal_date = :withdrawal_date,
                        withdrawal_reason = :withdrawal_reason,
                        updated_at = :updated_at
                    WHERE student_id = :student_id AND academic_session_id = :session_id
                """, {
                    'student_id': student_id,
                    'session_id': session_id,
                    'withdrawal_date': datetime.utcnow().date(),
                    'withdrawal_reason': withdrawal_reason,
                    'updated_at': datetime.utcnow()
                })
                
                # Update students_count in academic_sessions table
                db.session.execute("""
                    UPDATE academic_sessions 
                    SET students_count = (
                        SELECT COUNT(*) 
                        FROM student_academic_session_association 
                        WHERE academic_session_id = :session_id AND status = 'active'
                    )
                    WHERE id = :session_id
                """, {'session_id': session_id})
                
                db.session.commit()
                return True
        except Exception as e:
            with DatabaseContextManager() as db:
                db.session.rollback()
            print(f"Error withdrawing student from session: {e}")
            raise e

    def get_session_enrollment_stats(self, session_id: str):
        """Get enrollment statistics for an academic session"""
        try:
            with DatabaseContextManager() as db:
                stats = db.session.execute("""
                    SELECT 
                        COUNT(*) as total_enrollments,
                        SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active_enrollments,
                        SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed_enrollments,
                        SUM(CASE WHEN status = 'withdrawn' THEN 1 ELSE 0 END) as withdrawn_enrollments,
                        SUM(CASE WHEN status = 'suspended' THEN 1 ELSE 0 END) as suspended_enrollments,
                        SUM(CASE WHEN enrollment_type = 'regular' THEN 1 ELSE 0 END) as regular_enrollments,
                        SUM(CASE WHEN enrollment_type = 'part-time' THEN 1 ELSE 0 END) as part_time_enrollments,
                        SUM(CASE WHEN enrollment_type = 'audit' THEN 1 ELSE 0 END) as audit_enrollments,
                        SUM(CASE WHEN academic_standing = 'good' THEN 1 ELSE 0 END) as good_standing,
                        SUM(CASE WHEN academic_standing = 'probation' THEN 1 ELSE 0 END) as probation_count,
                        SUM(CASE WHEN academic_standing = 'warning' THEN 1 ELSE 0 END) as warning_count,
                        SUM(CASE WHEN academic_standing = 'suspended' THEN 1 ELSE 0 END) as suspended_count
                    FROM student_academic_session_association 
                    WHERE academic_session_id = :session_id
                """, {'session_id': session_id})
                
                row = stats.fetchone()
                if row:
                    return {
                        'total_enrollments': row[0] or 0,
                        'active_enrollments': row[1] or 0,
                        'completed_enrollments': row[2] or 0,
                        'withdrawn_enrollments': row[3] or 0,
                        'suspended_enrollments': row[4] or 0,
                        'regular_enrollments': row[5] or 0,
                        'part_time_enrollments': row[6] or 0,
                        'audit_enrollments': row[7] or 0,
                        'good_standing': row[8] or 0,
                        'probation_count': row[9] or 0,
                        'warning_count': row[10] or 0,
                        'suspended_count': row[11] or 0
                    }
                return {}
        except Exception as e:
            print(f"Error getting session enrollment stats: {e}")
            return {}