pages/enter_results.py

# pages/enter_results.py
import streamlit as st
import pandas as pd
import json
from datetime import datetime
from modules.database import Session, User, Student, AcademicTerm, ComponentMark, ENGINE, log_audit
from modules.grading import (calculate_compiled_score, convert_to_base, compute_total, 
                             get_grade, update_compiled_marks)

def show():
    st.header("✏️ Enter Results")
    session = Session()
    
    user = session.query(User).get(st.session_state.user_id)
    my_subjects = [s.strip() for s in user.subjects_taught.split(',')] if user.subjects_taught else []
    
    if not my_subjects:
        st.warning("You have no subjects assigned. Please contact administrator.")
        session.close()
        st.stop()
    
    active_term = session.query(AcademicTerm).filter_by(is_active=True).first()
    if not active_term:
        st.warning("No active term set. Please contact administrator.")
        session.close()
        st.stop()
    
    st.info(f"📅 Current Term: **{active_term.term_name}** (ID: {active_term.id})")
    
    # Select Class
    classes = pd.read_sql("SELECT DISTINCT class_name FROM students ORDER BY class_name", ENGINE)
    
    if classes.empty:
        st.info("No students enrolled yet")
        session.close()
        st.stop()
    
    st.markdown("### Step 1: Select Class")
    selected_class = st.selectbox("Class", classes['class_name'].tolist(), label_visibility="collapsed")
    
    # Select what to enter
    st.markdown("### Step 2: Select What to Enter")
    result_types = ["Coursework (CW)", "Mid of Term (MOT)", "End of Term (EOT)", "All Three"]
    selected_type = st.selectbox("Result Type", result_types, label_visibility="collapsed")
    
    # Select Subject
    st.markdown("### Step 3: Select Subject")
    selected_subject = st.selectbox("Subject", my_subjects, label_visibility="collapsed")
    
    # Get students in this class
    students_in_class = pd.read_sql(
        f"SELECT id, name, year, subjects FROM students WHERE class_name = '{selected_class}'",
        ENGINE
    )
    
    def has_subject(subjects_json, subject):
        try:
            subjects = json.loads(subjects_json)
            return subject in subjects
        except:
            return False
    
    students_in_class['has_subject'] = students_in_class['subjects'].apply(
        lambda x: has_subject(x, selected_subject)
    )
    students_with_subject = students_in_class[students_in_class['has_subject']]
    
    if students_with_subject.empty:
        st.warning(f"No students in {selected_class} take {selected_subject}")
        session.close()
        st.stop()
    
    st.markdown("---")
    st.subheader(f"Enter {selected_type} for {selected_class} - {selected_subject}")
    
    # Show existing marks for verification
    with st.expander("📊 View Existing Marks for This Class/Subject"):
        existing_marks_query = f"""
            SELECT s.name, m.coursework_out_of_20, m.midterm_out_of_20, 
                   m.endterm_out_of_60, m.total, m.grade
            FROM marks m
            JOIN students s ON m.student_id = s.id
            WHERE m.term_id = {active_term.id} 
            AND m.subject = '{selected_subject}'
            AND s.class_name = '{selected_class}'
            ORDER BY s.name
        """
        existing_marks_df = pd.read_sql(existing_marks_query, ENGINE)
        
        if not existing_marks_df.empty:
            st.dataframe(existing_marks_df, use_container_width=True)
        else:
            st.info("No marks entered yet for this class/subject combination")
    
    # Component marks entry system
    st.markdown("---")
    st.subheader("📝 Enter Component Marks")
    st.info("Enter individual test/paper scores below. The system will automatically compile them into the final scores.")
    
    # Select student
    student_name = st.selectbox("Select Student", students_with_subject['name'].tolist())
    student_id = int(students_with_subject[students_with_subject['name'] == student_name].iloc[0]['id'])
    
    # Tabs for different component types
    tab1, tab2, tab3, tab4 = st.tabs(["Coursework", "Mid-term", "End-term", "Compile Final Marks"])
    
    with tab1:
        if selected_type in ["Coursework (CW)", "All Three"]:
            st.markdown("### 📝 Coursework Components")
            
            # Get existing component marks for coursework
            cw_components = pd.read_sql(f"""
                SELECT id, component_name, score, total
                FROM component_marks
                WHERE student_id = {student_id} 
                AND subject = '{selected_subject}' 
                AND term_id = {active_term.id}
                AND component_type = 'coursework'
                ORDER BY component_name
            """, ENGINE)
            
            if not cw_components.empty:
                st.subheader("Existing Coursework Components")
                st.dataframe(cw_components, use_container_width=True)
            
            # Add new component
            with st.form("add_cw_component"):
                st.markdown("#### Add New Coursework Component")
                component_name = st.text_input("Component Name (e.g., Test 1, Assignment 1)")
                score = st.number_input("Score", 0.0, 1000.0, step=0.5)
                total = st.number_input("Total Marks", 0.1, 1000.0, step=0.5)
                
                if st.form_submit_button("Add Coursework Component"):
                    if component_name and total > 0:
                        new_component = ComponentMark(
                            student_id=student_id,
                            subject=selected_subject,
                            term_id=active_term.id,
                            component_type='coursework',
                            component_name=component_name,
                            score=score,
                            total=total,
                            submitted_by=st.session_state.user_id
                        )
                        session.add(new_component)
                        session.commit()
                        log_audit(session, st.session_state.user_id, "add_cw_component", 
                                 f"{student_name} - {selected_subject} - {component_name}")
                        st.success(f"✅ Coursework component added: {component_name}")
                        st.rerun()
                    else:
                        st.error("Please provide a component name and total marks")
            
            # Delete component
            if not cw_components.empty:
                st.markdown("#### Delete Coursework Component")
                component_to_delete = st.selectbox("Select Component to Delete", 
                                               cw_components['id'].tolist(), 
                                               format_func=lambda x: cw_components[cw_components['id']==x]['component_name'].iloc[0])
                
                if st.button("Delete Selected Component", key="delete_cw"):
                    component = session.query(ComponentMark).get(component_to_delete)
                    if component:
                        component_name = component.component_name
                        session.delete(component)
                        session.commit()
                        log_audit(session, st.session_state.user_id, "delete_cw_component", 
                                 f"{student_name} - {selected_subject} - {component_name}")
                        st.success(f"✅ Component deleted: {component_name}")
                        st.rerun()
    
    with tab2:
        if selected_type in ["Mid of Term (MOT)", "All Three"]:
            st.markdown("### 📋 Mid of Term Components")
            
            mt_components = pd.read_sql(f"""
                SELECT id, component_name, score, total
                FROM component_marks
                WHERE student_id = {student_id} 
                AND subject = '{selected_subject}' 
                AND term_id = {active_term.id}
                AND component_type = 'midterm'
                ORDER BY component_name
            """, ENGINE)
            
            if not mt_components.empty:
                st.subheader("Existing Mid-term Components")
                st.dataframe(mt_components, use_container_width=True)
            
            with st.form("add_mt_component"):
                st.markdown("#### Add New Mid-term Component")
                component_name = st.text_input("Component Name (e.g., Paper 1, Paper 2)")
                score = st.number_input("Score", 0.0, 1000.0, step=0.5)
                total = st.number_input("Total Marks", 0.1, 1000.0, step=0.5)
                
                if st.form_submit_button("Add Mid-term Component"):
                    if component_name and total > 0:
                        new_component = ComponentMark(
                            student_id=student_id,
                            subject=selected_subject,
                            term_id=active_term.id,
                            component_type='midterm',
                            component_name=component_name,
                            score=score,
                            total=total,
                            submitted_by=st.session_state.user_id
                        )
                        session.add(new_component)
                        session.commit()
                        log_audit(session, st.session_state.user_id, "add_mt_component", 
                                 f"{student_name} - {selected_subject} - {component_name}")
                        st.success(f"✅ Mid-term component added: {component_name}")
                        st.rerun()
                    else:
                        st.error("Please provide a component name and total marks")
            
            if not mt_components.empty:
                st.markdown("#### Delete Mid-term Component")
                component_to_delete = st.selectbox("Select Component to Delete", 
                                               mt_components['id'].tolist(), 
                                               format_func=lambda x: mt_components[mt_components['id']==x]['component_name'].iloc[0],
                                               key="mt_delete_select")
                
                if st.button("Delete Selected Component", key="delete_mt"):
                    component = session.query(ComponentMark).get(component_to_delete)
                    if component:
                        component_name = component.component_name
                        session.delete(component)
                        session.commit()
                        log_audit(session, st.session_state.user_id, "delete_mt_component", 
                                 f"{student_name} - {selected_subject} - {component_name}")
                        st.success(f"✅ Component deleted: {component_name}")
                        st.rerun()
    
    with tab3:
        if selected_type in ["End of Term (EOT)", "All Three"]:
            st.markdown("### 📄 End of Term Components")
            
            et_components = pd.read_sql(f"""
                SELECT id, component_name, score, total
                FROM component_marks
                WHERE student_id = {student_id} 
                AND subject = '{selected_subject}' 
                AND term_id = {active_term.id}
                AND component_type = 'endterm'
                ORDER BY component_name
            """, ENGINE)
            
            if not et_components.empty:
                st.subheader("Existing End-term Components")
                st.dataframe(et_components, use_container_width=True)
            
            with st.form("add_et_component"):
                st.markdown("#### Add New End-term Component")
                component_name = st.text_input("Component Name (e.g., Paper 1, Paper 2)")
                score = st.number_input("Score", 0.0, 1000.0, step=0.5)
                total = st.number_input("Total Marks", 0.1, 1000.0, step=0.5)
                
                if st.form_submit_button("Add End-term Component"):
                    if component_name and total > 0:
                        new_component = ComponentMark(
                            student_id=student_id,
                            subject=selected_subject,
                            term_id=active_term.id,
                            component_type='endterm',
                            component_name=component_name,
                            score=score,
                            total=total,
                            submitted_by=st.session_state.user_id
                        )
                        session.add(new_component)
                        session.commit()
                        log_audit(session, st.session_state.user_id, "add_et_component", 
                                 f"{student_name} - {selected_subject} - {component_name}")
                        st.success(f"✅ End-term component added: {component_name}")
                        st.rerun()
                    else:
                        st.error("Please provide a component name and total marks")
            
            if not et_components.empty:
                st.markdown("#### Delete End-term Component")
                component_to_delete = st.selectbox("Select Component to Delete", 
                                               et_components['id'].tolist(), 
                                               format_func=lambda x: et_components[et_components['id']==x]['component_name'].iloc[0],
                                               key="et_delete_select")
                
                if st.button("Delete Selected Component", key="delete_et"):
                    component = session.query(ComponentMark).get(component_to_delete)
                    if component:
                        component_name = component.component_name
                        session.delete(component)
                        session.commit()
                        log_audit(session, st.session_state.user_id, "delete_et_component", 
                                 f"{student_name} - {selected_subject} - {component_name}")
                        st.success(f"✅ Component deleted: {component_name}")
                        st.rerun()
    
    with tab4:
        st.markdown("### 🔄 Compile and Update Final Marks")
        
        # Get students with components
        students_with_components = pd.read_sql(f"""
            SELECT DISTINCT s.id, s.name
            FROM students s
            JOIN component_marks cm ON s.id = cm.student_id
            WHERE s.class_name = '{selected_class}'
            AND cm.subject = '{selected_subject}' 
            AND cm.term_id = {active_term.id}
            ORDER BY s.name
        """, ENGINE)
        
        if students_with_components.empty:
            st.info("No students with component marks found. Add component marks first.")
        else:
            selected_student_compile = st.selectbox("Select Student to Compile", 
                                                    students_with_components['name'].tolist(),
                                                    key="compile_student_select")
            student_id_compile = int(students_with_components[students_with_components['name'] == selected_student_compile].iloc[0]['id'])
            
            # Show current compiled scores
            cw_score, cw_total = calculate_compiled_score(session, student_id_compile, selected_subject, active_term.id, 'coursework')
            mt_score, mt_total = calculate_compiled_score(session, student_id_compile, selected_subject, active_term.id, 'midterm')
            et_score, et_total = calculate_compiled_score(session, student_id_compile, selected_subject, active_term.id, 'endterm')
            
            cw_out_of_20 = convert_to_base(cw_score, cw_total, 20)
            mt_out_of_20 = convert_to_base(mt_score, mt_total, 20)
            et_out_of_60 = convert_to_base(et_score, et_total, 60)
            
            total = compute_total(cw_out_of_20, mt_out_of_20, et_out_of_60)
            grade = get_grade(total)
            
            st.subheader(f"Compiled Scores for {selected_student_compile}")
            
            col1, col2, col3 = st.columns(3)
            with col1:
                st.metric("Coursework (out of 20)", f"{cw_out_of_20:.1f}")
                st.write(f"Based on {cw_score:.1f}/{cw_total:.1f}")
            with col2:
                st.metric("Mid-term (out of 20)", f"{mt_out_of_20:.1f}")
                st.write(f"Based on {mt_score:.1f}/{mt_total:.1f}")
            with col3:
                st.metric("End-term (out of 60)", f"{et_out_of_60:.1f}")
                st.write(f"Based on {et_score:.1f}/{et_total:.1f}")
            
            col4, col5 = st.columns(2)
            with col4:
                st.metric("Total (out of 100)", f"{total:.1f}")
            with col5:
                st.metric("Grade", grade)
            
            if st.button(f"💾 Update Final Marks for {selected_student_compile}", use_container_width=True):
                try:
                    total, grade = update_compiled_marks(session, student_id_compile, selected_subject, active_term.id)
                    log_audit(session, st.session_state.user_id, "update_compiled_marks", 
                             f"{selected_student_compile} - {selected_subject} - {active_term.term_name}")
                    st.success(f"✅ Final marks updated for {selected_student_compile}!")
                    st.success(f"📊 Total: {total:.1f}/100 | Grade: {grade}")
                    st.rerun()
                except Exception as e:
                    st.error(f"❌ Error updating marks: {str(e)}")
    
    session.close()