complete_report.py

import pandas as pd
import io
import matplotlib
matplotlib.use('Agg')
import matplotlib.pyplot as plt
from openpyxl.styles import Alignment, Font, Border, Side, PatternFill
from openpyxl.drawing.image import Image
from openpyxl.chart import BarChart, PieChart, Reference, Series, PieChart3D

def generate_complete_report_excel(results_data):
    from app import format_data_for_wide_export
    
    # 1. Prepare raw lists for DataFrames
    overview_data = []
    subject_details = []

    for student in results_data:
        usn = student.get('usn', '')
        name = student.get('student_name', '')
        sgpa_str = student.get('sgpa', '0')
        percentage_str = student.get('percentage', '0%')
        student_class = student.get('class', '')
        overall_result = 'P' if student_class != 'FAIL' else 'F'
        total_marks = student.get('total_marks', 0)
        
        try: sgpa = float(sgpa_str) if sgpa_str and str(sgpa_str).upper() != 'N/A' else 0.0
        except: sgpa = 0.0
            
        try: percentage = float(str(percentage_str).replace('%', '')) if percentage_str else 0.0
        except: percentage = 0.0
            
        try: total_marks_val = int(total_marks)
        except: total_marks_val = 0

        failed_subjects = []
        for sub in student.get('subjects', []):
            subj_code = sub.get('subject_code', '')
            subj_name = sub.get('subject_name', '')
            ia = sub.get('internal_marks', '0')
            ext = sub.get('external_marks', '0')
            tot = sub.get('total', '0')
            res = sub.get('result', '')
            
            try: ia_val = int(ia)
            except: ia_val = 0
            try: ext_val = int(ext)
            except: ext_val = 0
            try: tot_val = int(tot)
            except: tot_val = 0
            
            full_subject = f"{subj_code} - {subj_name}" if subj_name else subj_code
            if res.upper() in ['F', 'A', 'NE', 'X']:
                failed_subjects.append(full_subject)
                
            subject_details.append({
                'USN': usn, 'Name': name, 'Subject_Code': subj_code, 'Subject_Name': subj_name,
                'Full_Subject': full_subject, 'IA': ia_val, 'Ext': ext_val, 'Total': tot_val, 'Result': res.upper()
            })
            
        overview_data.append({
            'USN': usn, 'Name': name, 'Section': 'Unassigned', 'Marks': total_marks_val,
            'Percentage': percentage, 'Percentage_Str': percentage_str, 'SGPA': sgpa, 'Class': student_class,
            'Overall_Result': 'Pass' if overall_result == 'P' else 'Fail', 'Failed_Count': len(failed_subjects),
            'Failed_Subjects': ', '.join(failed_subjects) if failed_subjects else ''
        })

    df_students = pd.DataFrame(overview_data)
    df_subs = pd.DataFrame(subject_details)

    output = io.BytesIO()
    with pd.ExcelWriter(output, engine='openpyxl') as writer:
        if df_students.empty:
            pd.DataFrame({'Message': ['No data available']}).to_excel(writer, sheet_name='Summary', index=False)
            output.seek(0)
            return output

        header_font = Font(bold=True, color="FFFFFF")
        dark_fill = PatternFill(start_color="2F75B5", end_color="2F75B5", fill_type="solid")
        light_red_fill = PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type="solid")
        light_green_fill = PatternFill(start_color="C6EFCE", end_color="C6EFCE", fill_type="solid")
        center_align = Alignment(horizontal='center', vertical='center', wrap_text=True)
        thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin'))

        def format_sheet(ws, is_standard=True):
            if is_standard:
                for cell in ws[1]:
                    cell.font = header_font
                    cell.fill = dark_fill
                    cell.alignment = center_align
                    cell.border = thin_border
            for row in ws.iter_rows():
                for cell in row:
                    cell.alignment = center_align
                    cell.border = thin_border
            from openpyxl.utils import get_column_letter
            for col in ws.columns:
                max_length = 0
                column = get_column_letter(col[0].column)
                for cell in col:
                    try:
                        if len(str(cell.value)) > max_length:
                            max_length = len(cell.value)
                    except: pass
                ws.column_dimensions[column].width = min(max_length + 2, 50)

        def write_standard_sheet(df, sheet_name, extra_cols=None):
            cols = ['USN', 'Name', 'Section', 'Marks', 'Percentage_Str']
            rename_dict = {'USN': 'Student_ID', 'Percentage_Str': 'Percentage (%)'}
            if extra_cols: cols.extend(extra_cols)
            if df.empty:
                pd.DataFrame(columns=[rename_dict.get(c, c) for c in cols]).to_excel(writer, sheet_name=sheet_name, index=False)
            else:
                df_out = df[cols].copy()
                df_out.rename(columns=rename_dict, inplace=True)
                df_out.to_excel(writer, sheet_name=sheet_name, index=False)
            format_sheet(writer.sheets[sheet_name])

        # --- 1. Summary ---
        total_students = len(df_students)
        passed = len(df_students[df_students['Overall_Result'] == 'Pass'])
        failed = len(df_students[df_students['Overall_Result'] == 'Fail'])
        absent = 0
        pass_perc = (passed / total_students * 100) if total_students > 0 else 0
        
        fail_1 = len(df_students[df_students['Failed_Count'] == 1])
        fail_2 = len(df_students[df_students['Failed_Count'] == 2])
        fail_3_plus = len(df_students[df_students['Failed_Count'] >= 3])
        
        fcd = len(df_students[df_students['Class'] == 'FCD'])
        fc = len(df_students[df_students['Class'] == 'FC'])
        sc = len(df_students[df_students['Class'] == 'SC'])

        summary_rows = [
            ['Metric', 'Value'],
            ['Total', total_students],
            ['Appeared', total_students],
            ['Passed', passed],
            ['Failed', failed],
            ['Absent', absent],
            ['Pass %', f"{pass_perc:.2f}%"],
            ['', ''],
            ['── Failure Breakdown ──', ''],
            ['1 Subject Fail', fail_1],
            ['2 Subject Fails', fail_2],
            ['3+ Subject Fails', fail_3_plus],
            ['', ''],
            ['── Category Breakdown ──', ''],
            ['First Class Distinction (≥70%)', fcd],
            ['First Class (60-69.99%)', fc],
            ['Second Class (50-59.99%)', sc]
        ]
        df_summary = pd.DataFrame(summary_rows)
        df_summary.to_excel(writer, sheet_name='Summary', index=False, header=False)
        format_sheet(writer.sheets['Summary'], is_standard=False)
        
        # Color specific summary rows
        ws_sum = writer.sheets['Summary']
        for row in ws_sum.iter_rows():
            if row[0].value == 'Metric':
                for c in row: c.font = header_font; c.fill = dark_fill
            elif isinstance(row[0].value, str) and row[0].value.startswith('──'):
                for c in row: c.font = Font(bold=True); c.fill = PatternFill(start_color="D9D9D9", end_color="D9D9D9", fill_type="solid")

        # --- 2. Overview ---
        records, display_headers = format_data_for_wide_export(results_data)
        ws_over = writer.book.create_sheet('Overview')
        ws_over.cell(row=1, column=1, value='USN').font = header_font; ws_over.cell(row=1, column=1).fill = dark_fill; ws_over.merge_cells('A1:A2')
        ws_over.cell(row=1, column=2, value='Name').font = header_font; ws_over.cell(row=1, column=2).fill = dark_fill; ws_over.merge_cells('B1:B2')
        col_idx = 3
        for header_info in display_headers:
            is_elective = header_info["is_elective"]
            colspan = 5 if is_elective else 4
            ws_over.cell(row=1, column=col_idx, value=header_info["header"]).font = header_font
            ws_over.cell(row=1, column=col_idx).fill = dark_fill
            ws_over.merge_cells(start_row=1, start_column=col_idx, end_row=1, end_column=col_idx + colspan - 1)
            sub_headers = ['Course', 'IA', 'Ex', 'Total', 'Pass/Fail'] if is_elective else ['IA', 'Ex', 'Total', 'Pass/Fail']
            for i, sub_header in enumerate(sub_headers):
                cell = ws_over.cell(row=2, column=col_idx + i, value=sub_header)
                cell.font = header_font; cell.fill = dark_fill
            col_idx += colspan
        summary_start_col = col_idx
        summary_headers = ['NO OF SUBJECTS FAILED', 'NO OF SUBJECTS ABSENT', 'Percentage', 'Class', 'SGPA']
        for i, h in enumerate(summary_headers):
            cell = ws_over.cell(row=1, column=summary_start_col + i, value=h)
            cell.font = header_font; cell.fill = dark_fill
            ws_over.merge_cells(start_row=1, start_column=summary_start_col + i, end_row=2, end_column=summary_start_col + i)
        
        row_idx = 3
        for record in records:
            ws_over.cell(row=row_idx, column=1, value=record['USN'])
            ws_over.cell(row=row_idx, column=2, value=record['Name'])
            col_idx = 3
            for header_info in display_headers:
                header_key, is_elective = header_info["header"], header_info["is_elective"]
                data = record['subjects_data'][header_key]
                if is_elective:
                    ws_over.cell(row=row_idx, column=col_idx, value=data['Course'])
                    ws_over.cell(row=row_idx, column=col_idx + 1, value=data['IA'])
                    ws_over.cell(row=row_idx, column=col_idx + 2, value=data['Ex'])
                    ws_over.cell(row=row_idx, column=col_idx + 3, value=data['Total'])
                    pf_cell = ws_over.cell(row=row_idx, column=col_idx + 4, value=data['Pass/Fail'])
                    if data['Pass/Fail'] == 'F': pf_cell.fill = light_red_fill
                    col_idx += 5
                else:
                    ws_over.cell(row=row_idx, column=col_idx, value=data['IA'])
                    ws_over.cell(row=row_idx, column=col_idx + 1, value=data['Ex'])
                    ws_over.cell(row=row_idx, column=col_idx + 2, value=data['Total'])
                    pf_cell = ws_over.cell(row=row_idx, column=col_idx + 3, value=data['Pass/Fail'])
                    if data['Pass/Fail'] == 'F': pf_cell.fill = light_red_fill
                    col_idx += 4
            ws_over.cell(row=row_idx, column=col_idx, value=record['subjects_failed'])
            ws_over.cell(row=row_idx, column=col_idx + 1, value=record['subjects_absent'])
            ws_over.cell(row=row_idx, column=col_idx + 2, value=record['percentage'])
            class_cell = ws_over.cell(row=row_idx, column=col_idx + 3, value=record['class'])
            if record['class'] == 'FAIL': class_cell.fill = light_red_fill
            ws_over.cell(row=row_idx, column=col_idx + 4, value=record['sgpa'])
            row_idx += 1
        format_sheet(ws_over, is_standard=False)

        # --- 3. Ranking (Marks) & 4. Ranking (SGPA) ---
        df_rank_marks = df_students[['USN', 'Name', 'Marks', 'Percentage_Str', 'Class', 'Overall_Result']].copy()
        df_rank_marks.sort_values(by='Marks', ascending=False, inplace=True)
        df_rank_marks['Class_Rank'] = range(1, len(df_rank_marks) + 1)
        df_rank_marks.rename(columns={'USN': 'Student_ID', 'Percentage_Str': 'Percentage'}, inplace=True)
        df_rank_marks.to_excel(writer, sheet_name='Ranking (Marks)', index=False)
        format_sheet(writer.sheets['Ranking (Marks)'])

        df_rank_sgpa = df_students[['USN', 'Name', 'SGPA', 'Percentage_Str', 'Class', 'Overall_Result']].copy()
        df_rank_sgpa.sort_values(by='SGPA', ascending=False, inplace=True)
        df_rank_sgpa['SGPA_Rank'] = range(1, len(df_rank_sgpa) + 1)
        df_rank_sgpa.rename(columns={'USN': 'Student_ID', 'Percentage_Str': 'Percentage'}, inplace=True)
        df_rank_sgpa.to_excel(writer, sheet_name='Ranking (SGPA)', index=False)
        format_sheet(writer.sheets['Ranking (SGPA)'])

        # --- 5. Subject Analysis ---
        subj_analysis = []
        if not df_subs.empty:
            for subj in sorted(df_subs['Subject_Code'].unique()):
                sdf = df_subs[df_subs['Subject_Code'] == subj]
                total = len(sdf)
                absent_count = len(sdf[sdf['Result'] == 'A'])
                appeared_count = total - absent_count
                passed_count = len(sdf[sdf['Result'] == 'P'])
                failed_count = appeared_count - passed_count
                pass_p = (passed_count / appeared_count * 100) if appeared_count > 0 else 0
                avg_marks = sdf[sdf['Result'] != 'A']['Total'].mean() if appeared_count > 0 else 0
                subj_analysis.append({
                    'Subject': subj,
                    'Total': total,
                    'Appeared': appeared_count,
                    'Absent': absent_count,
                    'Passed': passed_count,
                    'Failed': failed_count,
                    'Pass %': round(pass_p, 2),
                    'Average Marks': round(avg_marks, 2)
                })
        df_subj_analysis = pd.DataFrame(subj_analysis)
        df_subj_analysis.to_excel(writer, sheet_name='Subject Analysis', index=False)
        ws_sa = writer.sheets['Subject Analysis']
        format_sheet(ws_sa)
        
        # Color pass % column logic like in the image (optional, basic formatting done)
        for row in ws_sa.iter_rows(min_row=2, max_row=ws_sa.max_row, min_col=1, max_col=8):
            row[6].fill = light_green_fill # Pass %
            row[7].fill = PatternFill(start_color="DCE6F1", end_color="DCE6F1", fill_type="solid") # Avg Marks

        # Add Charts to Subject Analysis
        if not df_subj_analysis.empty:
            
            # Determine dynamic row for charts (so they don't overlap table)
            chart_start_row = ws_sa.max_row + 4

            # Add summary stats box for Pass/Fail/Absent next to Pie (also acts as data source)
            ws_sa[f'V{chart_start_row}'] = 'Pass'; ws_sa[f'W{chart_start_row}'] = passed
            ws_sa[f'V{chart_start_row+1}'] = 'Fail'; ws_sa[f'W{chart_start_row+1}'] = failed
            ws_sa[f'V{chart_start_row+2}'] = 'Absent'; ws_sa[f'W{chart_start_row+2}'] = absent
            for r in range(chart_start_row, chart_start_row + 3):
                ws_sa[f'V{r}'].border = thin_border
                ws_sa[f'W{r}'].border = thin_border

            # 1. Pie Chart (Pass vs Fail Distribution)
            pie = PieChart()
            labels = Reference(ws_sa, min_col=22, min_row=chart_start_row, max_row=chart_start_row+2)
            data = Reference(ws_sa, min_col=23, min_row=chart_start_row-1, max_row=chart_start_row+2)
            pie.add_data(data, titles_from_data=False)
            pie.set_categories(labels)
            pie.title = "Pass vs Fail Distribution"
            pie.width = 10
            pie.height = 7
            ws_sa.add_chart(pie, f"A{chart_start_row}")

            # 2. Avg Marks Bar Chart
            bar_avg = BarChart()
            bar_avg.type = "col"
            bar_avg.style = 10
            bar_avg.title = "Average Total Marks per Subject"
            bar_avg.x_axis.title = "Subject"
            bar_avg.y_axis.title = "Marks"
            bar_avg.width = 12
            bar_avg.height = 7
            
            data_avg = Reference(ws_sa, min_col=8, min_row=1, max_row=ws_sa.max_row)
            cats = Reference(ws_sa, min_col=1, min_row=2, max_row=ws_sa.max_row)
            bar_avg.add_data(data_avg, titles_from_data=True)
            bar_avg.set_categories(cats)
            ws_sa.add_chart(bar_avg, f"G{chart_start_row}")

            # 3. Pass % Bar Chart
            bar_pass = BarChart()
            bar_pass.type = "col"
            bar_pass.style = 11
            bar_pass.title = "Subject-wise Pass Percentage"
            bar_pass.x_axis.title = "Subject"
            bar_pass.y_axis.title = "Pass %"
            bar_pass.width = 12
            bar_pass.height = 7
            
            data_pass = Reference(ws_sa, min_col=7, min_row=1, max_row=ws_sa.max_row)
            bar_pass.add_data(data_pass, titles_from_data=True)
            bar_pass.set_categories(cats)
            ws_sa.add_chart(bar_pass, f"P{chart_start_row}")

            # Add Subject Mapping Table (Place it below the charts)
            start_row = chart_start_row + 15
            ws_sa.cell(row=start_row, column=1, value='Subject Code').font = header_font
            ws_sa.cell(row=start_row, column=1).fill = dark_fill
            ws_sa.cell(row=start_row, column=2, value='Subject Name').font = header_font
            ws_sa.cell(row=start_row, column=2).fill = dark_fill
            
            unique_subjects = df_subs[['Subject_Code', 'Subject_Name']].drop_duplicates().sort_values('Subject_Code')
            for idx, (_, row) in enumerate(unique_subjects.iterrows()):
                ws_sa.cell(row=start_row + 1 + idx, column=1, value=row['Subject_Code']).border = thin_border
                ws_sa.cell(row=start_row + 1 + idx, column=2, value=row['Subject_Name']).border = thin_border

        # --- 6. Ineligible Students ---
        if not df_subs.empty:
            df_ineligible = df_subs[df_subs['Result'].isin(['A', 'NE', 'X'])][['Full_Subject', 'USN', 'Name', 'IA', 'Ext', 'Result']]
            df_ineligible.rename(columns={'Full_Subject': 'Subject', 'USN': 'Student ID', 'Ext': 'External'}, inplace=True)
            df_ineligible.to_excel(writer, sheet_name='Ineligible Students', index=False)
        else:
            pd.DataFrame(columns=['Subject', 'Student ID', 'Name', 'IA', 'External', 'Result']).to_excel(writer, sheet_name='Ineligible Students', index=False)
        format_sheet(writer.sheets['Ineligible Students'])

        # --- 7. Category Breakdown ---
        df_cat = df_students[df_students['Class'].isin(['FCD', 'FC', 'SC'])][['USN', 'Name', 'Marks', 'Percentage_Str', 'Class']].copy()
        class_map = {'FCD': 'FCD (First Class Distinction)', 'FC': 'First Class', 'SC': 'Second Class'}
        df_cat['Class'] = df_cat['Class'].map(class_map)
        df_cat.rename(columns={'USN': 'University Seat Number', 'Percentage_Str': 'Percentage', 'Class': 'Category'}, inplace=True)
        df_cat.to_excel(writer, sheet_name='Category Breakdown', index=False)
        format_sheet(writer.sheets['Category Breakdown'])

        # 8-11
        write_standard_sheet(df_students, 'Total Students')
        write_standard_sheet(df_students, 'Appeared')
        write_standard_sheet(df_students[df_students['Overall_Result'] == 'Pass'], 'Passed')
        write_standard_sheet(df_students[df_students['Overall_Result'] == 'Fail'], 'Failed', extra_cols=['Failed_Subjects'])
        
        # 12-14
        write_standard_sheet(df_students[df_students['Failed_Count'] == 1], '1 Subject Fail', extra_cols=['Failed_Subjects'])
        write_standard_sheet(df_students[df_students['Failed_Count'] == 2], '2 Subject Fails', extra_cols=['Failed_Subjects'])
        write_standard_sheet(df_students[df_students['Failed_Count'] >= 3], '3+ Subject Fails', extra_cols=['Failed_Subjects'])

        # 15-17
        write_standard_sheet(df_students[df_students['Class'] == 'FCD'], 'First Class Distinction')
        write_standard_sheet(df_students[df_students['Class'] == 'FC'], 'First Class')
        write_standard_sheet(df_students[df_students['Class'] == 'SC'], 'Second Class')

    output.seek(0)
    return output