pages/generate_reports.py

# pages/generate_reports.py
import streamlit as st
import pandas as pd
import io
import zipfile
from modules.database import Session, Student, AcademicTerm, ReportDesign, ENGINE, log_audit
from modules.pdf_generator import generate_pdf_report
from modules.grading import get_grade

def show():
    st.header("📄 Generate Student Reports")
    session = Session()
    
    students = pd.read_sql("SELECT * FROM students ORDER BY class_name, name", ENGINE)
    terms = pd.read_sql("SELECT * FROM academic_terms ORDER BY year DESC, term_number DESC", ENGINE)
    
    if students.empty or terms.empty:
        st.warning("Need students and terms to generate reports")
        session.close()
        st.stop()
    
    design = session.query(ReportDesign).first()
    
    st.subheader("Select Report Generation Options")
    
    report_mode = st.radio("Generate reports for:", ["Individual Student", "Whole Class"], horizontal=True)
    
    selected_term = st.selectbox("Select Term", terms['term_name'].tolist())
    term_data = terms[terms['term_name'] == selected_term].iloc[0]
    
    if report_mode == "Individual Student":
        selected_student = st.selectbox("Select Student", students['name'].tolist())
        student_data = students[students['name'] == selected_student].iloc[0]
        
        # Get marks
        marks_query = f"""
            SELECT m.*, u.name as teacher_name 
            FROM marks m
            JOIN users u ON m.submitted_by = u.id
            WHERE m.student_id = {student_data['id']} AND m.term_id = {term_data['id']}
            ORDER BY m.subject
        """
        marks = pd.read_sql(marks_query, ENGINE)
        
        # Get behavior data
        behavior_query = f"""
            SELECT punctuality, attendance, manners, general_behavior, 
                   organisational_skills, adherence_to_uniform, leadership_skills,
                   commitment_to_school, cooperation_with_peers, cooperation_with_staff,
                   participation_in_lessons, completion_of_homework
            FROM classroom_behavior
            WHERE student_id = {student_data['id']} AND term_id = {term_data['id']}
        """
        behavior_result = pd.read_sql(behavior_query, ENGINE)
        
        behavior_data = None
        if not behavior_result.empty:
            behavior_data = behavior_result.iloc[0].to_dict()
        
        # Get decision data (only for term 3)
        decision_data = None
        if term_data['term_number'] == 3:
            decision_query = f"""
                SELECT decision, notes
                FROM student_decisions
                WHERE student_id = {student_data['id']} AND term_id = {term_data['id']}
            """
            decision_result = pd.read_sql(decision_query, ENGINE)
            if not decision_result.empty:
                decision_data = decision_result.iloc[0].to_dict()
        
        if not marks.empty:
            st.subheader(f"Results Preview for {selected_student}")
            
            display_df = marks[[
                'subject', 'coursework_out_of_20', 'midterm_out_of_20', 
                'endterm_out_of_60', 'total', 'grade', 'comment', 'teacher_name'
            ]].copy()
            
            display_df.columns = ['Subject', 'CW/20', 'MOT/20', 'EOT/60', 'Total', 'Grade', 'Comment', 'Teacher']
            st.dataframe(display_df, use_container_width=True)
            
            overall_avg = marks['total'].mean()
            overall_grade = get_grade(overall_avg)
            
            avg_comments = {
                "A*": "Outstanding Performance",
                "A": "Excellent Performance",
                "B": "Good Performance",
                "C": "Satisfactory Performance",
                "D": "Needs Improvement",
                "E": "Poor Performance",
                "U": "Unsatisfactory Performance"
            }
            avg_comment = avg_comments.get(overall_grade, "")
            
            col3, col4 = st.columns(2)
            with col3:
                st.metric("Overall Average", f"{overall_avg:.0f}/100")
            with col4:
                st.metric("Overall Grade", f"{overall_grade} - {avg_comment}")
            
            if term_data['term_number'] == 3 and decision_data:
                st.metric("Decision", decision_data.get('decision', 'Pending'))
            
            if st.button("📄 Generate PDF Report", use_container_width=True):
                try:
                    pdf_data = generate_pdf_report(student_data, term_data, marks, design, behavior_data, decision_data)
                    
                    st.download_button(
                        "⬇️ Download PDF Report",
                        pdf_data,
                        f"{selected_student}_{selected_term}_report.pdf",
                        "application/pdf",
                        use_container_width=True
                    )
                    log_audit(session, st.session_state.user_id, "generate_report", 
                             f"Individual: {selected_student} - {selected_term}")
                    st.success("✅ PDF generated successfully!")
                except Exception as e:
                    st.error(f"❌ Error generating PDF: {str(e)}")
        else:
            st.info(f"❌ No marks found for {selected_student} in {selected_term}")
            st.info(f"Debug: Student ID = {student_data['id']}, Term ID = {term_data['id']}")
            
            any_marks = pd.read_sql(f"SELECT COUNT(*) as count FROM marks WHERE student_id = {student_data['id']}", ENGINE)
            st.info(f"This student has {any_marks['count'].iloc[0]} marks in total across all terms")
    
    else:  # Whole Class
        classes = pd.read_sql("SELECT DISTINCT class_name FROM students ORDER BY class_name", ENGINE)
        selected_class = st.selectbox("Select Class", classes['class_name'].tolist())
        
        class_students = students[students['class_name'] == selected_class]
        
        st.info(f"📊 Found {len(class_students)} students in {selected_class}")
        
        students_with_marks = []
        students_without_marks = []
        
        for _, student in class_students.iterrows():
            marks = pd.read_sql(f"""
                SELECT m.*, u.name as teacher_name 
                FROM marks m
                JOIN users u ON m.submitted_by = u.id
                WHERE m.student_id = {student['id']} AND m.term_id = {term_data['id']}
            """, ENGINE)
            
            if not marks.empty:
                students_with_marks.append(student['name'])
            else:
                students_without_marks.append(student['name'])
        
        col5, col6 = st.columns(2)
        with col5:
            st.success(f"✅ {len(students_with_marks)} students with marks")
        with col6:
            if students_without_marks:
                st.warning(f"⚠️ {len(students_without_marks)} students without marks")
        
        if students_without_marks:
            with st.expander("⚠️ Students without marks"):
                st.write(", ".join(students_without_marks))
        
        if students_with_marks:
            if st.button(f"📄 Generate All Reports for {selected_class}", use_container_width=True):
                zip_buffer = io.BytesIO()
                with zipfile.ZipFile(zip_buffer, 'w', zipfile.ZIP_DEFLATED) as zip_file:
                    for student_name in students_with_marks:
                        student_data = class_students[class_students['name'] == student_name].iloc[0]
                        
                        marks = pd.read_sql(f"""
                            SELECT m.*, u.name as teacher_name 
                            FROM marks m
                            JOIN users u ON m.submitted_by = u.id
                            WHERE m.student_id = {student_data['id']} AND m.term_id = {term_data['id']}
                        """, ENGINE)
                        
                        behavior_query = f"""
                            SELECT punctuality, attendance, manners, general_behavior, 
                                   organisational_skills, adherence_to_uniform, leadership_skills,
                                   commitment_to_school, cooperation_with_peers, cooperation_with_staff,
                                   participation_in_lessons, completion_of_homework
                            FROM classroom_behavior
                            WHERE student_id = {student_data['id']} AND term_id = {term_data['id']}
                        """
                        behavior_result = pd.read_sql(behavior_query, ENGINE)
                        
                        behavior_data = None
                        if not behavior_result.empty:
                            behavior_data = behavior_result.iloc[0].to_dict()
                        
                        decision_data = None
                        if term_data['term_number'] == 3:
                            decision_query = f"""
                                SELECT decision, notes
                                FROM student_decisions
                                WHERE student_id = {student_data['id']} AND term_id = {term_data['id']}
                            """
                            decision_result = pd.read_sql(decision_query, ENGINE)
                            if not decision_result.empty:
                                decision_data = decision_result.iloc[0].to_dict()
                        
                        if not marks.empty:
                            pdf_data = generate_pdf_report(student_data, term_data, marks, design, behavior_data, decision_data)
                            filename = f"{student_name}_{selected_term}_report.pdf"
                            zip_file.writestr(filename, pdf_data)
                
                zip_buffer.seek(0)
                
                st.download_button(
                    f"⬇️ Download All {len(students_with_marks)} Reports (ZIP)",
                    zip_buffer.getvalue(),
                    f"{selected_class}_{selected_term}_reports.zip",
                    "application/zip",
                    use_container_width=True
                )
                
                log_audit(session, st.session_state.user_id, "generate_reports", 
                         f"Bulk: {selected_class} - {selected_term} - {len(students_with_marks)} reports")
                st.success(f"✅ Generated {len(students_with_marks)} reports!")
        else:
            st.warning("No students in this class have marks for the selected term")
    
    session.close()