# exams/reports/tables_processor.py

from collections import defaultdict
from decimal import Decimal
import math

def process_district_registration_data(district_data, project_id):
    """
    Process district registration data for Jedwali Na. 2
    """
    processed = []
    total_reg_m = 0
    total_reg_f = 0
    total_reg_t = 0
    total_sat_m = 0
    total_sat_f = 0
    total_sat_t = 0
    total_abs_m = 0
    total_abs_f = 0
    total_abs_t = 0
    
    for district in district_data:
        reg_m = district.get('reg_m', 0)
        reg_f = district.get('reg_f', 0)
        reg_t = reg_m + reg_f
        
        sat_m = district.get('sat_m', 0)
        sat_f = district.get('sat_f', 0)
        sat_t = sat_m + sat_f
        
        abs_m = reg_m - sat_m
        abs_f = reg_f - sat_f
        abs_t = abs_m + abs_f
        
        sat_pct = (sat_t / reg_t * 100) if reg_t > 0 else 0
        abs_pct = (abs_t / reg_t * 100) if reg_t > 0 else 0
        
        processed.append({
            'name': district.get('name', ''),
            'school_count': district.get('schools', 0),
            'reg_m': reg_m,
            'reg_f': reg_f,
            'reg_t': reg_t,
            'sat_m': sat_m,
            'sat_f': sat_f,
            'sat_t': sat_t,
            'sat_pct': sat_pct,
            'abs_m': abs_m,
            'abs_f': abs_f,
            'abs_t': abs_t,
            'abs_pct': abs_pct
        })
        
        total_reg_m += reg_m
        total_reg_f += reg_f
        total_reg_t += reg_t
        total_sat_m += sat_m
        total_sat_f += sat_f
        total_sat_t += sat_t
        total_abs_m += abs_m
        total_abs_f += abs_f
        total_abs_t += abs_t
    
    totals = {
        'total_reg_m': total_reg_m,
        'total_reg_f': total_reg_f,
        'total_reg_t': total_reg_t,
        'total_sat_m': total_sat_m,
        'total_sat_f': total_sat_f,
        'total_sat_t': total_sat_t,
        'total_abs_m': total_abs_m,
        'total_abs_f': total_abs_f,
        'total_abs_t': total_abs_t,
        'overall_sat_pct': (total_sat_t / total_reg_t * 100) if total_reg_t > 0 else 0,
        'overall_abs_pct': (total_abs_t / total_reg_t * 100) if total_reg_t > 0 else 0
    }
    
    return processed, totals


def process_district_gpa_data(div_regions):
    """
    Process district GPA data for Jedwali Na. 5
    """
    processed = []
    
    for region in div_regions:
        sat = region.get('sat', 0)
        i_count = region.get('i', 0)
        ii_count = region.get('ii', 0)
        iii_count = region.get('iii', 0)
        iv_count = region.get('iv', 0)
        zero_count = region.get('o', 0)
        
        i_pct = (i_count / sat * 100) if sat > 0 else 0
        ii_pct = (ii_count / sat * 100) if sat > 0 else 0
        iii_pct = (iii_count / sat * 100) if sat > 0 else 0
        iv_pct = (iv_count / sat * 100) if sat > 0 else 0
        zero_pct = (zero_count / sat * 100) if sat > 0 else 0
        
        i_ii_count = i_count + ii_count
        i_ii_pct = (i_ii_count / sat * 100) if sat > 0 else 0
        
        i_iii_count = i_ii_count + iii_count
        i_iii_pct = (i_iii_count / sat * 100) if sat > 0 else 0
        
        i_iv_count = i_iii_count + iv_count
        i_iv_pct = (i_iv_count / sat * 100) if sat > 0 else 0
        
        processed.append({
            'name': region.get('region', ''),
            'sat': sat,
            'i': i_count,
            'i_pct': i_pct,
            'ii': ii_count,
            'ii_pct': ii_pct,
            'i_ii': i_ii_count,
            'i_ii_pct': i_ii_pct,
            'iii': iii_count,
            'iii_pct': iii_pct,
            'i_iii': i_iii_count,
            'i_iii_pct': i_iii_pct,
            'iv': iv_count,
            'iv_pct': iv_pct,
            'i_iv': i_iv_count,
            'i_iv_pct': i_iv_pct,
            'zero': zero_count,
            'zero_pct': zero_pct,
            'gpa': region.get('gpa', 0)
        })
    
    # Sort by GPA (lower is better)
    processed.sort(key=lambda x: x['gpa'])
    
    return processed


def process_school_ranking_data(school_ranking):
    """
    Process school ranking data for Jedwali Na. 30 and Na. 45
    """
    processed = []
    
    for idx, school in enumerate(school_ranking, 1):
        processed.append({
            'rank': idx,
            'name': school.get('name', ''),
            'short_name': school.get('name_short', school.get('name', '')),
            'code': school.get('code', ''),
            'district_name': school.get('district', ''),
            'region_name': school.get('region', ''),
            'ownership': school.get('ownership', ''),
            'sat': school.get('sat', 0) or school.get('with_results', 0),
            'i': school.get('i', 0) or school.get('d1', 0),
            'ii': school.get('ii', 0) or school.get('d2', 0),
            'iii': school.get('iii', 0) or school.get('d3', 0),
            'iv': school.get('iv', 0) or school.get('d4', 0),
            'zero': school.get('o', 0) or school.get('d0', 0),
            'gpa': school.get('gpa', 0) or school.get('sch_gpa', 0)
        })
    
    # Calculate derived fields
    for school in processed:
        sat = school['sat']
        if sat > 0:
            school['i_pct'] = (school['i'] / sat * 100)
            school['ii_pct'] = (school['ii'] / sat * 100)
            school['iii_pct'] = (school['iii'] / sat * 100)
            school['iv_pct'] = (school['iv'] / sat * 100)
            school['zero_pct'] = (school['zero'] / sat * 100)
            school['i_ii'] = school['i'] + school['ii']
            school['i_ii_pct'] = (school['i_ii'] / sat * 100)
            school['i_iii'] = school['i_ii'] + school['iii']
            school['i_iii_pct'] = (school['i_iii'] / sat * 100)
            school['i_iv'] = school['i_iii'] + school['iv']
            school['i_iv_pct'] = (school['i_iv'] / sat * 100)
        else:
            school['i_pct'] = 0
            school['ii_pct'] = 0
            school['iii_pct'] = 0
            school['iv_pct'] = 0
            school['zero_pct'] = 0
            school['i_ii'] = 0
            school['i_ii_pct'] = 0
            school['i_iii'] = 0
            school['i_iii_pct'] = 0
            school['i_iv'] = 0
            school['i_iv_pct'] = 0
    
    # Sort by GPA for overall ranking
    processed.sort(key=lambda x: x['gpa'])
    
    return processed


def process_subject_category_data(combination_performance, student_final_results):
    """
    Process subject category data for Jedwali Na. 18-21
    """
    categories = {}
    
    # Define category mapping
    science_codes = ['PCM', 'PCB', 'CBG', 'CBA', 'PMCs']
    arts_codes = ['HGK', 'HGL', 'HKL']
    business_codes = ['ECA', 'EGM', 'HGE']
    language_codes = ['Kiswahili', 'English']
    
    for combo in combination_performance:
        code = combo.get('code', '')
        
        if code in science_codes:
            category = 'Sayansi'
        elif code in arts_codes:
            category = 'Sanaa'
        elif code in business_codes:
            category = 'Biashara na Uchumi'
        elif code in language_codes:
            category = 'Lugha'
        else:
            category = 'Nyingine'
        
        if category not in categories:
            categories[category] = {
                'name': category,
                'total': 0,
                'i': 0,
                'ii': 0,
                'iii': 0,
                'iv': 0,
                'zero': 0
            }
        
        categories[category]['total'] += combo.get('total_students', 0)
        
        # Add division counts from grade_distribution if available
        grade_dist = combo.get('grade_distribution', {})
        categories[category]['i'] += grade_dist.get('I', 0)
        categories[category]['ii'] += grade_dist.get('II', 0)
        categories[category]['iii'] += grade_dist.get('III', 0)
        categories[category]['iv'] += grade_dist.get('IV', 0)
        categories[category]['zero'] += grade_dist.get('0', 0)
    
    # Calculate percentages
    for cat in categories.values():
        total = cat['total']
        if total > 0:
            cat['i_pct'] = (cat['i'] / total * 100)
            cat['ii_pct'] = (cat['ii'] / total * 100)
            cat['iii_pct'] = (cat['iii'] / total * 100)
            cat['iv_pct'] = (cat['iv'] / total * 100)
            cat['zero_pct'] = (cat['zero'] / total * 100)
        else:
            cat['i_pct'] = cat['ii_pct'] = cat['iii_pct'] = cat['iv_pct'] = cat['zero_pct'] = 0
    
    # Calculate totals
    totals = {
        'total': sum(c['total'] for c in categories.values()),
        'i': sum(c['i'] for c in categories.values()),
        'ii': sum(c['ii'] for c in categories.values()),
        'iii': sum(c['iii'] for c in categories.values()),
        'iv': sum(c['iv'] for c in categories.values()),
        'zero': sum(c['zero'] for c in categories.values())
    }
    
    total_all = totals['total']
    if total_all > 0:
        totals['i_pct'] = (totals['i'] / total_all * 100)
        totals['ii_pct'] = (totals['ii'] / total_all * 100)
        totals['iii_pct'] = (totals['iii'] / total_all * 100)
        totals['iv_pct'] = (totals['iv'] / total_all * 100)
        totals['zero_pct'] = (totals['zero'] / total_all * 100)
    
    return list(categories.values()), totals


def process_top_students_data(best_students):
    """
    Process top students data for Jedwali Na. 22-29
    """
    processed = []
    
    for idx, student in enumerate(best_students, 1):
        processed.append({
            'rank': idx,
            'full_name': student.get('full_name', ''),
            'sex': student.get('sex', ''),
            'district_name': student.get('district', ''),
            'school_name': student.get('school', ''),
            'average': student.get('average', 0) or student.get('avg', 0),
            'points': student.get('points', 0),
            'division': student.get('division', '')
        })
    
    return processed


def format_number(value):
    """Format number with commas"""
    try:
        return f"{int(value):,}"
    except (ValueError, TypeError):
        return str(value)


def format_percent(value, decimals=2):
    """Format percentage"""
    try:
        return f"{float(value):.{decimals}f}%"
    except (ValueError, TypeError):
        return "0.00%"


def format_gpa(value, decimals=4):
    """Format GPA with 4 decimals"""
    try:
        return f"{float(value):.{decimals}f}"
    except (ValueError, TypeError):
        return "0.0000"