# server/app.py

from flask import Flask, request, jsonify, session, Response
from flask_cors import CORS
from flask_login import LoginManager, login_user, logout_user, login_required, current_user
from datetime import timedelta, datetime
from models import (
    db, User, EnglishClass, EnglishLevel, PrivateSchoolClass,
    EnglishBook, BookPurchaseReceipt, EnglishRegistrationReceipt,
    RegistrationFee, Link_Reset, FormStock, EnglishRole, PrivateFeeItem,
    PrivateRole, EnglishStudent, EnglishSession, PrivateFeeAssignment,
    PrivateSession, PrivateStudent, PrivateEnrollmentReceipt,
    PrivateBook, PrivateBookReceipt, PrivateUniform, PrivateUniformReceipt,
    PrivateFormStock, PrivateFormReceipt, EnglishSessionTime, EnglishSessionPeriod,
    IslamiyyaFeeSetting, IslamiyyaPaymentReceipt
    
)
from functions import (
    requires_roles, require_password_changed,
    get_student_by_search, generate_receipt_number, 
    is_valid_email, build_english_registration_receipt,
    private_school_classes_to_code, private_school_code_to_display,
    get_next_color_index, get_next_pass_number, SHORT_CODE_TO_CLASS_CODE,
    CLASS_CODE_TO_SHORT, get_class_name_from_code, get_class_display_name_from_code,
    format_session_label, get_class_codes_for_group
)
from gate_pass_file import (
    process_college_pass, colors, color_map, process_college_pass_english,
)
import os
from io import BytesIO
import uuid
import time
from constants_file import BASE_URL_FRONTEND
from mail_file import send_reset_email, send_mail_vercel, send_new_user_message
from itsdangerous import URLSafeTimedSerializer
from flask_mail import Mail
from init_data import initialize_database
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, numbers, PatternFill, Border, Side
from io import BytesIO
from datetime import datetime, timezone, timedelta
import openpyxl
from flask_migrate import Migrate
import pandas as pd


app = Flask(__name__)
app.config['SECRET_KEY'] = 'your-secret-keysdsdsdss-change-in-production'
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///school_management.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config["SESSION_COOKIE_NAME"] = "app2_session"
app.config['REMEMBER_COOKIE_DURATION'] = timedelta(days=7)

mail = Mail(app)
CORS(app, supports_credentials=True)
db.init_app(app)
migrate = Migrate(app, db)
login_manager = LoginManager(app)
login_manager.login_view = 'login'
s = URLSafeTimedSerializer(app.config['SECRET_KEY'])

# Mail configuration (optional)
app.config['MAIL_SERVER'] = 'smtp.gmail.com'
app.config['MAIL_PORT'] = 587
app.config['MAIL_USE_TLS'] = True
app.config['MAIL_USERNAME'] = os.environ.get('MAIL_USERNAME')
app.config['MAIL_PASSWORD'] = os.environ.get('MAIL_PASSWORD')
app.config['MAIL_DEFAULT_SENDER'] = os.environ.get('MAIL_DEFAULT_SENDER')

@login_manager.user_loader
def load_user(user_id):
    return User.query.get(user_id)

@login_manager.unauthorized_handler
def unauthorized():
    return jsonify({'error': 'Authentication required.'}), 403

@app.route('/')
def home():
    return jsonify({"greetings": "Hello"})
# ==================== AUTH ROUTES ====================

@app.route('/api/login', methods=['POST'])
def login():
    data = request.get_json()
    email = data.get('email').lower()
    password = data.get('password')
    
    user = User.query.filter_by(email=email).first()
    if user and user.check_password(password):
        login_user(user)
        return jsonify({
            'success': True,
            'user': {
                'id': user.id,
                'name': user.name,
                'email': user.email,
                'english_roles': [role.name for role in user.english_roles],
                'private_roles': [role.name for role in user.private_roles],
                'must_change_password': user.must_change_password
            }
        })
    return jsonify({'error': 'Invalid credentials.'}), 401

@app.route('/api/change-password', methods=['POST'])
@login_required
def change_password():
    data = request.get_json()
    current_password = data.get('current_password')
    new_password = data.get('new_password')
    
    if current_user.check_password(current_password):
        if new_password == '123456':
            return jsonify({'error': 'Cannot change back to default password.'}), 400
        if len(new_password) < 6:
            return jsonify({'error': 'Password must be at least 6 characters.'}), 400
        current_user.set_password(new_password)
        current_user.must_change_password = False
        db.session.commit()
        return jsonify({'success': True})
    return jsonify({'error': 'Current password is incorrect.'}), 401

@app.route('/api/logout', methods=['POST'])
@login_required
def logout():
    logout_user()
    return jsonify({'success': True})

@app.route('/api/current-user', methods=['GET'])
@login_required
def current_user_info():
    return jsonify({
        'id': current_user.id,
        'name': current_user.name,
        'email': current_user.email,
        'english_roles': [role.name for role in current_user.english_roles],
        'private_roles': [role.name for role in current_user.private_roles],
        'must_change_password': current_user.must_change_password
    })


# ==================== USER MANAGEMENT (Director) ====================

@app.route('/api/all-users', methods=['GET'])
@login_required
def get_all_users():
    users = User.query.all()
    return jsonify([{
        'id': u.id,
        'name': u.name,
        'email': u.email,
        'english_roles': [r.name for r in u.english_roles],
        'private_roles': [r.name for r in u.private_roles],
        'created_at': u.created_at.isoformat()
    } for u in users])

@app.route('/api/english/create-user', methods=['POST'])
@login_required
@require_password_changed
@requires_roles('english', 'Director')
def create_english_user():
    data = request.get_json()
    email = data.get('email').lower()
    name = data.get('name')
    
    if User.query.filter_by(email=email).first():
        return jsonify({'error': 'Email already exists.'}), 400
    
    user = User(
        name=name,
        email=email,
        must_change_password=True
    )
    user.set_password('123456')
    
    # Assign English roles only
    role_names = data.get('roles', [])
    for role_name in role_names:
        role = EnglishRole.query.filter_by(name=role_name).first()
        if role:
            user.english_roles.append(role)
    
    msg = send_new_user_message(email, name)
    try:
        res = send_mail_vercel(msg)
        if not res.get("status"):
            raise Exception(res.get("error"))
    except Exception as e:
        if str(e) == '[Errno 11001] getaddrinfo failed':
            error = 'Failed to connect. Please check your internet connection.'
        else:
            error = f'Error: {str(e)}'
        return jsonify({'error': f'{error}'}), 400

    db.session.add(user)
    db.session.commit()
    
    return jsonify({'success': True, 'user_id': user.id})

@app.route('/api/update-user/<user_id>', methods=['PUT'])
@login_required
@require_password_changed
@requires_roles('english', 'Director')
def update_user(user_id):
    user = User.query.get(user_id)
    if not user:
        return jsonify({'error': 'User not found'}), 404
    
    data = request.get_json()
    
    # Update name (applies to both systems)
    if 'name' in data:
        user.name = data['name']
    
    # Update English roles if provided
    if 'english_roles' in data:
        # Check if trying to remove Director role from English system
        current_has_director = any(role.name == 'Director' for role in user.english_roles)
        new_has_director = 'Director' in data['english_roles']
        
        if current_has_director and not new_has_director:
            # Count total English directors in system
            total_directors = User.query.filter(User.english_roles.any(name='Director')).count()
            
            if total_directors <= 1:
                return jsonify({'error': 'Cannot remove the only English Director from the system'}), 400
        
        # Update English roles
        user.english_roles = []
        for role_name in data['english_roles']:
            role = EnglishRole.query.filter_by(name=role_name).first()
            if role:
                user.english_roles.append(role)
    
    # Update Private roles if provided
    if 'private_roles' in data:
        # Check if trying to remove Director role from Private system
        current_has_director = any(role.name == 'Director' for role in user.private_roles)
        new_has_director = 'Director' in data['private_roles']
        
        if current_has_director and not new_has_director:
            # Count total Private directors in system
            total_directors = User.query.filter(User.private_roles.any(name='Director')).count()
            
            if total_directors <= 1:
                return jsonify({'error': 'Cannot remove the only Private Director from the system'}), 400
        
        # Update Private roles
        user.private_roles = []
        for role_name in data['private_roles']:
            role = PrivateRole.query.filter_by(name=role_name).first()
            if role:
                user.private_roles.append(role)
    
    db.session.commit()
    return jsonify({'success': True})

# ==================== CLASS AND FEE MANAGEMENT ====================

@app.route('/api/english-classes', methods=['GET'])
@login_required
def get_english_classes():
    classes = EnglishClass.query.all()
    return jsonify([{
        'id': c.id,
        'name': c.name,
        'code': c.code,
        'levels': [{'id': l.id, 'name': l.name, 'level_order': l.level_order} for l in c.levels]
    } for c in classes])

@app.route('/api/private-school-classes', methods=['GET'])
@login_required
def get_private_school_classes():
    classes = PrivateSchoolClass.query.all()
    return jsonify([{
        'id': c.id,
        'name': c.name,
        'code': c.code
    } for c in classes])

@app.route('/api/fees', methods=['GET'])
@login_required
def get_fees():
    # Get form fees from FormStock table
    form_stock = FormStock.query.all()
    reg_fees = RegistrationFee.query.all()
    
    form_fees_data = []
    for form in form_stock:
        # Get all levels for this class
        levels = EnglishLevel.query.filter_by(class_code=form.class_code).order_by(EnglishLevel.level_order).all()
        
        if levels and len(levels) > 0:
            # Class has levels - create form fee entry for EVERY level
            for level in levels:
                form_fees_data.append({
                    'id': form.id,
                    'class_code': form.class_code,
                    'level_order': level.level_order,
                    'amount': float(form.price)
                })
        else:
            # Class has no levels - don't include level_order
            form_fees_data.append({
                'id': form.id,
                'class_code': form.class_code,
                'amount': float(form.price)
            })
    
    return jsonify({
        'form_fees': form_fees_data,
        'registration_fees': [{'id': f.id, 'class_code': f.class_code, 'amount': float(f.amount)} for f in reg_fees]
    })

@app.route('/api/fees/registration', methods=['POST'])
@login_required
@require_password_changed
@requires_roles('english', 'Cashier')
def set_registration_fee():
    data = request.get_json()
    class_code = data.get('class_code')
    amount = data.get('amount')
    
    fee = RegistrationFee.query.filter_by(class_code=class_code).first()
    if fee:
        fee.amount = amount
    else:
        fee = RegistrationFee(class_code=class_code, amount=amount)
        db.session.add(fee)
    
    db.session.commit()
    return jsonify({'success': True})

# ==================== BOOK MANAGEMENT (InventoryManager) ====================

@app.route('/api/english/books', methods=['GET'])
@login_required
def get_english_books():
    # Get all English books (both english class books and general books)
    books = EnglishBook.query.order_by(EnglishBook.created_at.desc()).all()
    return jsonify([{
        'id': b.id,
        'title': b.title,
        'author': b.author,
        'price': b.price,
        'quantity': b.quantity,
        'class_type': b.class_type,
        'class_id': b.class_id,
        'class_name': b.class_name,
        'level': b.level,
        'student_status': b.student_status,
        'created_at': b.created_at.isoformat(),
        'updated_at': b.updated_at.isoformat()
    } for b in books])


@app.route('/api/english/create-book', methods=['POST'])
@login_required
@require_password_changed
@requires_roles('english', 'InventoryManager')
def create_english_book():
    data = request.get_json()

    title = (data.get('title') or '').strip()
    author = (data.get('author') or '').strip()
    price = data.get('price')
    quantity = data.get('quantity')
    class_type = data.get('class_type')  # Must be explicitly 'english' or 'general'
    class_id = data.get('class_id') or ''
    class_name = data.get('class_name') or ''
    level = data.get('level') or ''
    student_status = data.get('student_status') or ''

    errors = {}

    # Title
    if not title:
        errors['title'] = 'Title is required'

    # Author
    if not author:
        errors['author'] = 'Author is required'

    # Price
    try:
        price = float(price)
        if price <= 0:
            errors['price'] = 'Price must be greater than 0'
    except (TypeError, ValueError):
        errors['price'] = 'Invalid price'

    # Quantity
    try:
        quantity = int(quantity)
        if quantity < 0:
            errors['quantity'] = 'Quantity cannot be negative'
    except (TypeError, ValueError):
        errors['quantity'] = 'Invalid quantity'

    # Class type validation - MUST be explicitly chosen
    if not class_type:
        errors['class_type'] = 'Book type (English Class or General) must be selected'
    elif class_type not in ['english', 'general']:
        errors['class_type'] = 'Invalid book type. Must be "english" or "general"'
    elif class_type == 'english':
        if not class_id:
            errors['class_id'] = 'Class must be selected'
        if not level:
            errors['level'] = 'Level must be selected'
        if not student_status or student_status not in ['fresh', 'returning']:
            errors['student_status'] = 'Student status must be selected'
    elif class_type == 'general':
        # Clear fields for general books
        class_id = ''
        class_name = ''
        level = ''
        student_status = ''

    if errors:
        return jsonify({'success': False, 'errors': errors}), 400

    book = EnglishBook(
        title=title,
        author=author,
        price=price,
        quantity=quantity,
        class_type=class_type,
        class_id=class_id,
        class_name=class_name,
        level=level,
        student_status=student_status
    )

    db.session.add(book)
    db.session.commit()

    return jsonify({'success': True, 'book_id': book.id})


@app.route('/api/english/update-book/<book_id>', methods=['PUT'])
@login_required
@require_password_changed
@requires_roles('english', 'InventoryManager')
def update_english_book(book_id):
    book = EnglishBook.query.get(book_id)
    if not book:
        return jsonify({'error': 'Book not found'}), 404
    
    data = request.get_json()

    errors = {}

    title = (data.get('title') or '').strip()
    author = (data.get('author') or '').strip()
    price = data.get('price')
    quantity = data.get('quantity')
    class_type = data.get('class_type')
    class_id = data.get('class_id') or ''
    class_name = data.get('class_name') or ''
    level = data.get('level') or ''
    student_status = data.get('student_status') or ''

    # Title
    if not title:
        errors['title'] = 'Title is required'

    # Author
    if not author:
        errors['author'] = 'Author is required'

    # Price
    try:
        price = float(price)
        if price <= 0:
            errors['price'] = 'Price must be greater than 0'
    except (TypeError, ValueError):
        errors['price'] = 'Invalid price'

    # Quantity
    try:
        quantity = int(quantity)
        if quantity < 0:
            errors['quantity'] = 'Quantity cannot be negative'
    except (TypeError, ValueError):
        errors['quantity'] = 'Invalid quantity'

    # Class type validation
    if not class_type:
        errors['class_type'] = 'Book type (English Class or General) must be selected'
    elif class_type not in ['english', 'general']:
        errors['class_type'] = 'Invalid book type. Must be "english" or "general"'
    elif class_type == 'english':
        if not class_id:
            errors['class_id'] = 'Class must be selected'
        if not level:
            errors['level'] = 'Level must be selected'
        if not student_status or student_status not in ['fresh', 'returning']:
            errors['student_status'] = 'Student status must be selected'
    elif class_type == 'general':
        class_id = ''
        class_name = ''
        level = ''
        student_status = ''

    if errors:
        return jsonify({'success': False, 'errors': errors}), 400

    # Check for actual changes
    has_changes = False

    if book.title != title:
        book.title = title
        has_changes = True

    if book.author != author:
        book.author = author
        has_changes = True

    if float(book.price) != price:
        book.price = price
        has_changes = True

    if int(book.quantity) != quantity:
        book.quantity = quantity
        has_changes = True

    if (book.class_type or '') != class_type:
        book.class_type = class_type
        has_changes = True

    if (book.class_id or '') != class_id:
        book.class_id = class_id
        has_changes = True

    if (book.class_name or '') != class_name:
        book.class_name = class_name
        has_changes = True

    if (book.level or '') != level:
        book.level = level
        has_changes = True

    if (book.student_status or '') != student_status:
        book.student_status = student_status
        has_changes = True

    if not has_changes:
        return jsonify({
            'success': False,
            'message': 'No changes were made'
        }), 400

    db.session.commit()

    return jsonify({'success': True})

@app.route('/api/english/delete-book/<book_id>', methods=['DELETE'])
@login_required
@require_password_changed
@requires_roles('english', 'InventoryManager')
def delete_english_book(book_id):
    book = EnglishBook.query.get(book_id)
    if not book:
        return jsonify({'error': 'Book not found'}), 404

    db.session.delete(book)
    db.session.commit()

    return jsonify({'success': True, 'message': 'Book deleted successfully'}), 200

# ==================== STUDENT MANAGEMENT & ENROLLMENT ====================

@app.route('/api/english/students/search', methods=['GET'])
@login_required
def search_english_students():
    query = request.args.get('q', '')
    students = get_student_by_search("english", query)
    return jsonify([{
        'id': s.id,
        'name': s.name,
        'phone': s.phone,
        'email': s.email,
        'class_code': s.class_code,
        'level_name': s.level_name,
        'level_order': s.level_order,
        "created_at": s.created_at.isoformat(),
        "updated_at": s.updated_at.isoformat()
    } for s in students])



@app.route('/api/english/all-students', methods=['GET'])
@login_required
def get_all_english_students():
    try:
        students = EnglishStudent.query.all()
        result = [student.to_dict() for student in students]
        result.sort(key=lambda x: x['name'].lower())
        return jsonify(result), 200
    except Exception as e:
        return jsonify({'error': str(e)}), 500

    
# ==================== STUDENT MANAGEMENT (Edit/Update) ====================

@app.route('/api/english/update-student/<student_id>', methods=['PUT'])
@login_required
@requires_roles('english', 'Cashier')
def update_student(student_id):
    """Update student information (name, phone, email)"""
    try:
        student = EnglishStudent.query.get(student_id)
        if not student:
            return jsonify({'error': 'Student not found'}), 
    
        data = request.get_json() or {}

        new_name = (data.get('name') or '').strip()
        new_phone = (data.get('phone') or '').strip()
        new_email = (data.get('email') or '').strip()

        # Required fields (email is NOT required)
        if not new_name or not new_phone:
            return jsonify({
                'success': False,
                'message': 'Name and phone are required'
            }), 400

        # Email validation only if provided
        if new_email and not is_valid_email(new_email):
            return jsonify({
                'success': False,
                'message': 'Enter a valid email address'
            }), 400

        # Check if nothing changed
        if (
            new_name == (student.name or '').strip() and
            new_phone == (student.phone or '').strip() and
            new_email == (student.email or '').strip()
        ):
            return jsonify({
                'success': False,
                'message': 'No changes were made'
            }), 400

        student.name = new_name
        student.phone = new_phone
        student.email = new_email if new_email else None

        db.session.commit()

        # class name resolution (same as GET route)
        class_name = None
        if student.class_code:
            english_class = EnglishClass.query.filter_by(code=student.class_code).first()
            class_name = english_class.name if english_class else None

        return jsonify({
            'success': True,
            'student': student.to_dict(),
        }), 200

    except Exception as e:
        return jsonify({
            'success': False,
            'error': str(e)
        }), 500


@app.route('/api/english/<student_id>/enroll', methods=['POST'])
@login_required
@require_password_changed
@requires_roles('english', 'Cashier')
def enroll_existing_student(student_id):
    """Enroll an existing student who is not in any class"""    
    student = EnglishStudent.query.get(student_id)
    if not student:
        return jsonify({'error': 'Student not found'}), 404
    
    data = request.get_json()
    
    class_code = data.get('class_code')
    level_order = data.get('level_order')
    payment_method = data.get('payment_method')
    discount_amount = data.get('discount_amount', 0)
    discount_reason = data.get('discount_reason', '')
    discount_type = data.get('discount_type', 'fixed')
    discount_value = data.get('discount_value', 0)
    session_id = data.get('session_id')

    # Get fee selection flags (default to True for backward compatibility)
    charge_form_fee = data.get('charge_form_fee')
    charge_registration_fee = data.get('charge_registration_fee')

    if charge_form_fee is None or charge_registration_fee is None:
        return jsonify({'error': 'charge_form_fee and charge_registration_fee fields are required'}), 400

    if session_id is None:
        return jsonify({'error': 'session_id field is required'}), 400
    
    session = EnglishSession.query.get(session_id)
    if not session:
        return jsonify({'error': 'Invalid session'}), 400
    
    # Validate discount
    if discount_amount < 0:
        return jsonify({'error': 'Discount amount cannot be negative'}), 400
    
    # Validate at least one fee is selected
    if not charge_form_fee and not charge_registration_fee:
        return jsonify({'error': 'At least one fee (Form Fee or Registration Fee) must be selected'}), 400
    
    if not class_code:
        return jsonify({'error': 'Class is required'}), 400
    
    if student.is_enrolled:
        return jsonify({'error': 'Student is already enrolled. Use change class instead.'}), 400
    
    english_class = EnglishClass.query.filter_by(code=class_code).first()
    if not english_class:
        return jsonify({'error': 'Invalid class'}), 400
    
    levels = EnglishLevel.query.filter_by(class_code=class_code).order_by(EnglishLevel.level_order).all()
    has_levels = len(levels) > 0
    
    level = None
    if has_levels:
        if level_order is None:
            return jsonify({'error': 'Level is required for this class'}), 400
        level = EnglishLevel.query.filter_by(class_code=class_code, level_order=level_order).first()
        if not level:
            return jsonify({'error': 'Invalid level'}), 400
    else:
        level_order = None
    
    # Get form fee and registration fee from tables
    form_stock = FormStock.query.filter_by(class_code=class_code).first()
    reg_fee = RegistrationFee.query.filter_by(class_code=class_code).first()
    
    # Calculate subtotal based on selected fees (regardless of level)
    form_fee_amount = 0
    registration_fee_amount = 0
    subtotal = 0
    
    # Charge form fee if selected (regardless of level)
    if charge_form_fee:
        if not form_stock:
            return jsonify({
                'error': f'Form fee not configured for class {class_code}. Please contact administrator to set up form fees.'
            }), 400
        form_fee_amount = float(form_stock.price)
        subtotal += form_fee_amount
    
    # Charge registration fee if selected (regardless of level)
    if charge_registration_fee:
        if not reg_fee:
            return jsonify({
                'error': f'Registration fee not configured for class {class_code}. Please contact administrator to set up registration fees.'
            }), 400
        registration_fee_amount = float(reg_fee.amount)
        subtotal += registration_fee_amount
    
    # Calculate actual discount from percentage if needed
    actual_discount = discount_amount
    if discount_type == 'percentage' and discount_value > 0:
        actual_discount = (subtotal * min(discount_value, 100)) / 100
    
    # Validate discount doesn't exceed subtotal
    if actual_discount > subtotal:
        return jsonify({
            'error': f'Discount amount (₦{actual_discount:,.2f}) cannot exceed subtotal (₦{subtotal:,.2f})',
            'max_allowed_discount': subtotal
        }), 400
    
    # Calculate total amount
    total_amount = subtotal - actual_discount
    
    # Require reason for large discounts (>50%)
    discount_percentage = (actual_discount / subtotal * 100) if subtotal > 0 else 0
    if discount_percentage > 0 and not discount_reason:
        return jsonify({
            'error': f'Discount reason required.',
            'discount_percentage': discount_percentage
        }), 400

    # DEDUCT FORM STOCK ONLY IF form fee is charged
    if charge_form_fee and form_stock:
        if form_stock.stock_quantity <= 0:
            return jsonify({
                'error': f'No forms in stock for {class_code}. Please restock before enrolling new students.'
            }), 400
        form_stock.stock_quantity -= 1

    # Create receipt using build function
    receipt = build_english_registration_receipt(
        class_code=class_code,
        student_id=student.id,
        level_name=level.name if level else None,
        level_order=level_order,
        session_id=session_id,
        form_fee_amount=form_fee_amount,
        registration_fee_amount=registration_fee_amount,
        actual_discount=actual_discount,
        discount_reason=discount_reason,
        total_amount=total_amount,
        payment_method=payment_method,
        cashier_id=current_user.id,
        receipt_type='enrollment',
        charge_form_fee=charge_form_fee,
        charge_registration_fee=charge_registration_fee
    )
    
    # Update student info
    student.class_code = class_code
    student.level_name = level.name if level else None
    student.level_order = level_order
    student.english_class_id = english_class.id
    student.is_enrolled = True
    
    try:
        db.session.add(receipt)
        db.session.commit()
        
        return jsonify({
            'success': True,
            'message': 'Student enrolled successfully',
            'receipt': receipt.to_dict(),
            'student': student.to_dict(),
        }), 200
        
    except Exception as e:
        db.session.rollback()
        return jsonify({'error': f'Failed to enroll student: {str(e)}'}), 500
    


@app.route('/api/english/<student_id>/change-class', methods=['PUT'])
@login_required
@require_password_changed
@requires_roles('english', 'Cashier')
def change_english_student_class(student_id):
    """Change student to a different class and level"""
    student = EnglishStudent.query.get(student_id)
    if not student:
        return jsonify({'error': 'Student not found'}), 404
    
    data = request.get_json()
    
    new_class_code = data.get('class_code')
    new_level_order = data.get('level_order')
    payment_method = data.get('payment_method')
    discount_amount = data.get('discount_amount', 0)
    discount_reason = data.get('discount_reason', '')
    discount_type = data.get('discount_type', 'fixed')
    discount_value = data.get('discount_value', 0)
    session_id = data.get('session_id')
    
    # Get fee selection flags (default to True for backward compatibility)
    charge_form_fee = data.get('charge_form_fee')
    charge_registration_fee = data.get('charge_registration_fee')

    if charge_form_fee is None or charge_registration_fee is None:
        return jsonify({'error': 'charge_form_fee and charge_registration_fee fields are required'}), 400

    if session_id is None:
        return jsonify({'error': 'session_id field is required'}), 400
    
    session = EnglishSession.query.get(session_id)
    if not session:
        return jsonify({'error': 'Invalid session'}), 400
    
    if discount_amount < 0:
        return jsonify({'error': 'Discount amount cannot be negative'}), 400
    
    # Validate at least one fee is selected
    if not charge_form_fee and not charge_registration_fee:
        return jsonify({'error': 'At least one fee (Form Fee or Registration Fee) must be selected'}), 400
    
    if not new_class_code:
        return jsonify({'error': 'Class is required'}), 400
    
    if not student.is_enrolled:
        return jsonify({'error': 'Student is not enrolled. Use enroll instead.'}), 400
    
    if student.class_code == new_class_code:
        if new_level_order is not None and student.level_order == new_level_order:
            return jsonify({'error': 'No changes were made.'}), 400
        elif new_level_order is None and student.level_order is None:
            return jsonify({'error': 'No changes were made.'}), 400
    
    english_class = EnglishClass.query.filter_by(code=new_class_code).first()
    if not english_class:
        return jsonify({'error': 'Invalid class'}), 400
    
    levels = EnglishLevel.query.filter_by(class_code=new_class_code).order_by(EnglishLevel.level_order).all()
    has_levels = len(levels) > 0
    
    level = None
    if has_levels:
        if new_level_order is None:
            return jsonify({'error': 'Level is required for this class'}), 400
        level = EnglishLevel.query.filter_by(class_code=new_class_code, level_order=new_level_order).first()
        if not level:
            return jsonify({'error': 'Invalid level'}), 400
    else:
        new_level_order = None
    
    # Get form fee and registration fee from tables
    form_stock = FormStock.query.filter_by(class_code=new_class_code).first()
    reg_fee = RegistrationFee.query.filter_by(class_code=new_class_code).first()
    
    # Calculate subtotal based on selected fees (regardless of level)
    form_fee_amount = 0
    registration_fee_amount = 0
    subtotal = 0
    
    # Charge form fee if selected (regardless of level)
    if charge_form_fee:
        if not form_stock:
            return jsonify({
                'error': f'Form fee not configured for class {new_class_code}. Please contact administrator to set up form fees.'
            }), 400
        form_fee_amount = float(form_stock.price)
        subtotal += form_fee_amount
        
        # DEDUCT FORM STOCK if form fee is charged
        if form_stock.stock_quantity <= 0:
            return jsonify({
                'error': f'No forms in stock for {new_class_code}. Please restock before processing class change.'
            }), 400
        form_stock.stock_quantity -= 1
    
    # Charge registration fee if selected (regardless of level)
    if charge_registration_fee:
        if not reg_fee:
            return jsonify({
                'error': f'Registration fee not configured for class {new_class_code}. Please contact administrator to set up registration fees.'
            }), 400
        registration_fee_amount = float(reg_fee.amount)
        subtotal += registration_fee_amount
    
    # Calculate actual discount
    actual_discount = discount_amount
    if discount_type == 'percentage' and discount_value > 0:
        actual_discount = (subtotal * min(discount_value, 100)) / 100
    
    if actual_discount > subtotal:
        return jsonify({
            'error': f'Discount amount (₦{actual_discount:,.2f}) cannot exceed subtotal (₦{subtotal:,.2f})',
            'max_allowed_discount': subtotal
        }), 400
    
    total_amount = subtotal - actual_discount
    
    discount_percentage = (actual_discount / subtotal * 100) if subtotal > 0 else 0
    if discount_percentage > 0 and not discount_reason:
        return jsonify({
            'error': f'Discount reason required.',
            'discount_percentage': discount_percentage
        }), 400

    receipt = build_english_registration_receipt(
        class_code=new_class_code,
        student_id=student.id,
        level_name=level.name if level else None,
        level_order=new_level_order,
        session_id=session_id,
        form_fee_amount=form_fee_amount,
        registration_fee_amount=registration_fee_amount,
        actual_discount=actual_discount,
        discount_reason=discount_reason,
        total_amount=total_amount,
        payment_method=payment_method,
        cashier_id=current_user.id,
        receipt_type='class_change',
        charge_form_fee=charge_form_fee,
        charge_registration_fee=charge_registration_fee
    )

    old_class = student.class_code
    old_level = student.level_name
    
    student.class_code = new_class_code
    student.level_name = level.name if level else None
    student.level_order = new_level_order
    student.english_class_id = english_class.id
    
    try:
        db.session.add(receipt)
        db.session.commit()
        
        return jsonify({
            'success': True,
            'message': 'Student class changed successfully',
            'receipt': receipt.to_dict(),
            'student': student.to_dict(),
            'previous_class': old_class,
            'previous_level': old_level,
        }), 200
        
    except Exception as e:
        db.session.rollback()
        return jsonify({'error': f'Failed to change class: {str(e)}'}), 500

@app.route('/api/calculate-fees/<class_code>', methods=['GET'])
@login_required
@require_password_changed
@requires_roles('english', 'Cashier')
def calculate_fees(class_code):
    """Calculate fees for enrollment, class change with fee selection options"""
    level_order = request.args.get('level_order', type=int)
    fee_type = request.args.get('type', 'enrollment')
    
    # Get query params as strings and convert to boolean properly
    charge_form_fee_param = request.args.get('charge_form_fee')
    charge_registration_fee_param = request.args.get('charge_registration_fee')
    
    # Handle the parameters - they come as strings 'true'/'false'
    if charge_form_fee_param is None:
        return jsonify({'error': 'charge_form_fee parameter is required'}), 400
    if charge_registration_fee_param is None:
        return jsonify({'error': 'charge_registration_fee parameter is required'}), 400
    
    charge_form_fee = charge_form_fee_param.lower() == 'true'
    charge_registration_fee = charge_registration_fee_param.lower() == 'true'
    
    form_fee_amount = 0
    registration_fee_amount = 0
    
    # Get form fee from FormStock table if requested
    if charge_form_fee:
        form_stock = FormStock.query.filter_by(class_code=class_code).first()
        if form_stock:
            form_fee_amount = float(form_stock.price)
    
    # Get registration fee if requested
    if charge_registration_fee:
        reg_fee = RegistrationFee.query.filter_by(class_code=class_code).first()
        if reg_fee:
            registration_fee_amount = float(reg_fee.amount)
    
    subtotal = form_fee_amount + registration_fee_amount
    
    # Check stock availability
    form_stock = FormStock.query.filter_by(class_code=class_code).first()
    reg_fee = RegistrationFee.query.filter_by(class_code=class_code).first()
    
    return jsonify({
        'form_fee': form_fee_amount,
        'registration_fee': registration_fee_amount,
        'subtotal': subtotal,
        'has_stock': form_stock.stock_quantity > 0 if form_stock else False,
        'stock_quantity': form_stock.stock_quantity if form_stock else 0,
        'has_form_fee_config': form_stock is not None,
        'has_reg_fee_config': reg_fee is not None
    })

@app.route('/api/enroll-english', methods=['POST'])
@login_required
@require_password_changed
@requires_roles('english', 'Cashier')
def enroll_english_student():
    data = request.get_json()
    
    # Get new student data if provided (instead of student_id)
    student_id = data.get('student_id')
    new_student_data = data.get('new_student_data')
    class_code = data.get('class_code')
    level_order = data.get('level_order')
    session_id = data.get('session_id')
    payment_method = data.get('payment_method')
    discount_amount = data.get('discount_amount', 0)
    discount_reason = data.get('discount_reason', '')

    # REQUIRED: Explicit flag to determine if student should be enrolled
    should_enroll = data.get('should_enroll')
    if should_enroll is None:
        return jsonify({'error': 'should_enroll field is required (true/false)'}), 400

    # Validate session if provided
    if session_id is None:
        return jsonify({'error': 'session_id field is required'}), 400
    
    session = EnglishSession.query.get(session_id)
    if not session:
        return jsonify({'error': 'Invalid session'}), 400
    
    # Get fee selection flags (default to True for backward compatibility)
    charge_form_fee = data.get('charge_form_fee')
    charge_registration_fee = data.get('charge_registration_fee')

    if charge_form_fee is None or charge_registration_fee is None:
        return jsonify({'error': 'charge_form_fee and charge_registration_fee fields are required'}), 400

    # SANITY CHECK 1: Discount cannot be negative
    if discount_amount < 0:
        return jsonify({'error': 'Discount amount cannot be negative'}), 400
    
    # SANITY CHECK 2: At least one fee must be charged
    if not charge_form_fee and not charge_registration_fee:
        return jsonify({'error': 'At least one fee (Form Fee or Registration Fee) must be selected'}), 400
    
    # SANITY CHECK 3: If should_enroll is True, class_code is required
    if should_enroll and not class_code:
        return jsonify({'error': 'class_code is required when should_enroll is true'}), 400
    
    student = None
    
    # Case 1: New student provided (from frontend)
    if new_student_data and not student_id:
        name = (new_student_data.get('name') or '').strip()
        phone = (new_student_data.get('phone') or '').strip()
        email = (new_student_data.get('email') or '').strip()

        # Required fields
        if not name or not phone:
            return jsonify({'error': 'Name, phone, are required'}), 400

        # Email validation (ONLY if provided)
        if email and not is_valid_email(email=email):
            return jsonify({'error': 'Invalid email address'}), 400
        
        # Create new student
        new_english_student = EnglishStudent(
            name=name,
            phone=phone,
            email=email or None,
            is_enrolled=should_enroll
        )
        db.session.add(new_english_student)
        db.session.flush()  # Get ID without committing
        student_id = new_english_student.id
        student = new_english_student
        
    # Case 2: Existing student
    elif student_id:
        student = EnglishStudent.query.get(student_id)
        if not student:
            return jsonify({'error': 'Student not found'}), 404
    else:
        return jsonify({'error': 'Student information is required'}), 400
    
    english_class = None
    level = None

    if class_code:
        english_class = EnglishClass.query.filter_by(code=class_code).first()
        if not english_class:
            return jsonify({'error': 'Invalid class code'}), 400
            
        if level_order:
            level = EnglishLevel.query.filter_by(class_code=class_code, level_order=level_order).first()
            if not level:
                return jsonify({'error': 'Invalid level'}), 400
    
    # Calculate fees - NOW ALWAYS APPLIES REGARDLESS OF LEVEL
    subtotal = 0
    form_fee_amount = 0
    registration_fee_amount = 0
    form_stock = None
    
    # Get form fee if charge_form_fee is True (regardless of level)
    if charge_form_fee:
        form_stock = FormStock.query.filter_by(class_code=class_code).first()
        if not form_stock:
            return jsonify({
                'error': f'Form fee not configured for class {class_code}. Please contact administrator to set up form fees.'
            }), 400
        form_fee_amount = float(form_stock.price)
        subtotal += form_fee_amount
    
    # Get registration fee if charge_registration_fee is True (regardless of level)
    if charge_registration_fee:
        reg_fee = RegistrationFee.query.filter_by(class_code=class_code).first()
        if not reg_fee:
            return jsonify({
                'error': f'Registration fee not configured for class {class_code}. Please contact administrator to set up registration fees.'
            }), 400
        registration_fee_amount = float(reg_fee.amount)
        subtotal += registration_fee_amount
    
    # SANITY CHECK 3: Discount cannot exceed subtotal
    if discount_amount > subtotal:
        return jsonify({
            'error': f'Discount amount (₦{discount_amount:,.2f}) cannot exceed subtotal (₦{subtotal:,.2f})',
            'max_allowed_discount': subtotal
        }), 400
    
    # SANITY CHECK 4: Calculate discount percentage for additional validation
    discount_percentage = (discount_amount / subtotal * 100) if subtotal > 0 else 0
    
    # SANITY CHECK 5: Require reason for large discounts (>50%)
    if discount_percentage > 0 and not discount_reason:
        return jsonify({
            'error': f'Discount reason required.',
            'discount_percentage': discount_percentage
        }), 400
    
    # Calculate total (now safe because we validated discount_amount <= subtotal)
    total_amount = subtotal - discount_amount
    
    # DEDUCT FORM STOCK ONLY IF form fee is charged AND we have form_stock
    if charge_form_fee and form_stock:
        if form_stock.stock_quantity <= 0:
            return jsonify({
                'error': f'No forms in stock for {class_code}. Please restock before enrolling students.'
            }), 400
        form_stock.stock_quantity -= 1
    
    # Create receipt
    receipt = build_english_registration_receipt(
        class_code=class_code if class_code else '',
        student_id=student_id,
        level_name=level.name if level else None,
        level_order=level_order,
        session_id=session_id,
        form_fee_amount=form_fee_amount,
        registration_fee_amount=registration_fee_amount,
        actual_discount=discount_amount,
        discount_reason=discount_reason,
        total_amount=total_amount,
        payment_method=payment_method,
        cashier_id=current_user.id,
        receipt_type="enrollment" if should_enroll else "payment_only",
        charge_form_fee=charge_form_fee,
        charge_registration_fee=charge_registration_fee
    )

    # Mark which fees were actually paid (only for payment_only receipts)
    if not should_enroll:
        receipt.form_fee_paid = charge_form_fee
        receipt.registration_fee_paid = charge_registration_fee
    
    if should_enroll:
        student.class_code = class_code
        student.level_name = level.name if level else None
        student.level_order = level_order
        student.english_class_id = english_class.id if english_class else None
        student.is_enrolled = True
    
    db.session.add(receipt)
    db.session.commit()

    print("receipt:::")
    print(receipt.to_dict())
    
    return jsonify({
        'success': True, 
        'receipt': receipt.to_dict()
    })

@app.route('/api/english/<student_id>/assign-class', methods=['POST'])
@login_required
@require_password_changed
@requires_roles('english', 'Cashier')
def assign_class_to_paid_student(student_id):
    """Assign a class to a student who has already paid but is not enrolled"""    
    student = EnglishStudent.query.get(student_id)
    if not student:
        return jsonify({'error': 'Student not found'}), 404
    
    data = request.get_json()
    
    class_code = data.get('class_code')
    level_order = data.get('level_order')
    
    # Validate required fields
    if class_code is None:
        return jsonify({'error': 'class_code is required'}), 400
    
    if not class_code:
        return jsonify({'error': 'Class is required'}), 400
    
    # Check if student is already enrolled
    if student.is_enrolled:
        return jsonify({'error': 'Student is already enrolled. Use change class instead.'}), 400
    
    # Get class info
    english_class = EnglishClass.query.filter_by(code=class_code).first()
    if not english_class:
        return jsonify({'error': 'Invalid class'}), 400
    
    # Check if class has levels
    levels = EnglishLevel.query.filter_by(class_code=class_code).order_by(EnglishLevel.level_order).all()
    has_levels = len(levels) > 0
    
    level = None
    if has_levels:
        if level_order is None:
            return jsonify({'error': 'Level is required for this class'}), 400
        level = EnglishLevel.query.filter_by(class_code=class_code, level_order=level_order).first()
        if not level:
            return jsonify({'error': 'Invalid level'}), 400
    else:
        level_order = None
    
    # Update student info
    student.class_code = class_code
    student.level_name = level.name if level else None
    student.level_order = level_order
    student.english_class_id = english_class.id
    student.is_enrolled = True
    
    try:
        db.session.commit()
        
        return jsonify({
            'success': True,
            'message': 'Class assigned successfully',
            'student': student.to_dict()
        }), 200
        
    except Exception as e:
        db.session.rollback()
        return jsonify({'error': f'Failed to assign class: {str(e)}'}), 500

@app.route('/api/english/<student_id>/assign-class-with-payment', methods=['POST'])
@login_required
@require_password_changed
@requires_roles('english', 'Cashier')
def assign_class_with_payment(student_id):
    """Assign class to paid student, collecting any unpaid fees"""    
    student = EnglishStudent.query.get(student_id)
    if not student:
        return jsonify({'error': 'Student not found'}), 404
    
    data = request.get_json()
    
    class_code = data.get('class_code')
    level_order = data.get('level_order')
    payment_method = data.get('payment_method')
    discount_amount = data.get('discount_amount', 0)
    discount_reason = data.get('discount_reason', '')
    session_id = data.get('session_id')
    
    # Get the payment-only receipt to check what was paid
    payment_receipt = EnglishRegistrationReceipt.query.filter_by(
        student_id=student_id,
        receipt_type='payment_only'
    ).order_by(EnglishRegistrationReceipt.created_at.desc()).first()
    
    if not payment_receipt:
        return jsonify({'error': 'No payment receipt found for this student'}), 400

    # Validate session if provided
    if session_id is None:
        return jsonify({'error': 'session_id field is required'}), 400
    
    session = EnglishSession.query.get(session_id)
    if not session:
        return jsonify({'error': 'Invalid session'}), 400
    
    if not class_code:
        return jsonify({'error': 'Class is required'}), 400
    
    if student.is_enrolled:
        return jsonify({'error': 'Student is already enrolled'}), 400
    
    english_class = EnglishClass.query.filter_by(code=class_code).first()
    if not english_class:
        return jsonify({'error': 'Invalid class'}), 400
    
    levels = EnglishLevel.query.filter_by(class_code=class_code).order_by(EnglishLevel.level_order).all()
    has_levels = len(levels) > 0
    
    level = None
    if has_levels:
        if level_order is None:
            return jsonify({'error': 'Level is required for this class'}), 400
        level = EnglishLevel.query.filter_by(class_code=class_code, level_order=level_order).first()
        if not level:
            return jsonify({'error': 'Invalid level'}), 400
    else:
        level_order = None
    
    # Calculate what fees still need to be paid
    form_fee_already_paid = payment_receipt.form_fee_paid
    registration_fee_already_paid = payment_receipt.registration_fee_paid
    
    form_stock = FormStock.query.filter_by(class_code=class_code).first()
    reg_fee = RegistrationFee.query.filter_by(class_code=class_code).first()
    
    additional_form_fee = 0
    additional_registration_fee = 0
    
    # Charge form fee if not already paid
    if not form_fee_already_paid:
        if not form_stock:
            return jsonify({'error': f'Form fee not configured for class {class_code}'}), 400
        additional_form_fee = float(form_stock.price)
        if form_stock.stock_quantity <= 0:
            return jsonify({'error': f'No forms in stock for {class_code}'}), 400
        form_stock.stock_quantity -= 1
    
    # Charge registration fee if not already paid
    if not registration_fee_already_paid:
        if not reg_fee:
            return jsonify({'error': f'Registration fee not configured for class {class_code}'}), 400
        additional_registration_fee = float(reg_fee.amount)
    
    subtotal = additional_form_fee + additional_registration_fee
    
    if discount_amount > subtotal:
        return jsonify({'error': f'Discount cannot exceed subtotal'}), 400
    
    total_amount = subtotal - discount_amount
    
    # Create receipt for additional payment if any
    receipt = None
    if subtotal > 0:
        receipt = build_english_registration_receipt(
            class_code=class_code,
            student_id=student.id,
            level_name=level.name if level else None,
            level_order=level_order,
            session_id=session_id,
            form_fee_amount=additional_form_fee,
            registration_fee_amount=additional_registration_fee,
            actual_discount=discount_amount,
            discount_reason=discount_reason,
            total_amount=total_amount,
            payment_method=payment_method,
            cashier_id=current_user.id,
            receipt_type='enrollment',
            charge_form_fee=not form_fee_already_paid,
            charge_registration_fee=not registration_fee_already_paid
        )
        db.session.add(receipt)
    
    # Update student - now enrolled
    student.class_code = class_code
    student.level_name = level.name if level else None
    student.level_order = level_order
    student.english_class_id = english_class.id
    student.is_enrolled = True
    
    db.session.commit()
    
    return jsonify({
        'success': True,
        'message': 'Class assigned successfully',
        'receipt': receipt.to_dict() if receipt else None,
        'student': student.to_dict(),
        'fees_paid_previously': {
            'form_fee': form_fee_already_paid,
            'registration_fee': registration_fee_already_paid
        },
        'additional_payment_required': subtotal > 0,
        'additional_amount': total_amount
    }), 200

@app.route('/api/english/purchase-books', methods=['POST'])
@login_required
@require_password_changed
@requires_roles('english', 'Cashier')
def purchase_english_books():
    data = request.get_json()

    student_id = data.get('student_id')
    new_student_data = data.get('new_student_data')
    items = data.get('items')
    payment_method = data.get('payment_method')
    discount_amount = data.get('discount_amount', 0)
    discount_reason = data.get('discount_reason', '')
    discount_type = data.get('discount_type', 'fixed')
    discount_value = data.get('discount_value', 0)
    session_id = data.get('session_id')

    # Validate discount
    if discount_amount < 0:
        return jsonify({'error': 'Discount amount cannot be negative'}), 400

    if not items or not isinstance(items, list):
        return jsonify({'error': 'No items provided'}), 400
    
    if session_id is None:
        return jsonify({'error': 'session_id field is required'}), 400
    
    session = EnglishSession.query.get(session_id)
    if not session:
        return jsonify({'error': 'Invalid session'}), 400

    # Start transaction
    try:
        student = None
        
        # Case 1: New student provided (from frontend)
        if new_student_data and not student_id:
            name = (new_student_data.get('name') or '').strip()
            phone = (new_student_data.get('phone') or '').strip()
            email = (new_student_data.get('email') or '').strip()

            # Required fields
            if not name or not phone:
                return jsonify({'error': 'Name, phone, are required'}), 400

            # Email validation (ONLY if provided)
            if email and not is_valid_email(email=email):
                return jsonify({'error': 'Invalid email address'}), 400
            
            # Create new student
            new_english_student = EnglishStudent(
                name=name,
                phone=phone,
                email=email or None
            )
            db.session.add(new_english_student)
            db.session.flush()
            student_id = new_english_student.id
            student = new_english_student
            
        # Case 2: Existing student
        elif student_id:
            student = EnglishStudent.query.get(student_id)
            if not student:
                return jsonify({'error': 'Student not found'}), 404
        else:
            return jsonify({'error': 'Student ID is required'}), 400

        # Process items and calculate totals
        subtotal = 0
        purchase_items = []

        for item in items:
            book = EnglishBook.query.get(item.get('book_id'))

            if not book:
                return jsonify({'error': 'Book not found'}), 404

            qty = item.get('quantity')

            if not qty or qty <= 0:
                return jsonify({'error': 'Invalid quantity'}), 400

            if book.quantity < qty:
                return jsonify({'error': f'Not enough stock for {book.title}'}), 400

            item_total = book.price * qty
            subtotal += item_total

            purchase_items.append({
                'book_id': book.id,
                'title': book.title,
                'quantity': qty,
                'price': float(book.price),
                'class_name': book.class_name,
                'class_type': book.class_type,
                'total': float(item_total)
            })

            book.quantity -= qty

        # Calculate actual discount from percentage if needed
        actual_discount = discount_amount
        if discount_type == 'percentage' and discount_value > 0:
            actual_discount = (subtotal * min(discount_value, 100)) / 100

        # Validate discount doesn't exceed subtotal
        if actual_discount > subtotal:
            return jsonify({
                'error': f'Discount amount (₦{actual_discount:,.2f}) cannot exceed subtotal (₦{subtotal:,.2f})',
                'max_allowed_discount': subtotal
            }), 400

        total_amount = subtotal - actual_discount

        # Require reason for large discounts (>50%)
        discount_percentage = (actual_discount / subtotal * 100) if subtotal > 0 else 0
        if discount_percentage > 0 and not discount_reason:
            return jsonify({
                'error': f'Discount reason required.',
                'discount_percentage': discount_percentage
            }), 400

        # Create receipt
        receipt = BookPurchaseReceipt(
            receipt_number=generate_receipt_number(),
            student_id=student.id,
            total_amount=total_amount,
            discount_amount=actual_discount,
            discount_reason=discount_reason,
            payment_method=payment_method,
            cashier_id=current_user.id,
            items=purchase_items,
            session_id=session_id,
        )

        db.session.add(receipt)
        
        # Commit everything together
        db.session.commit()

        return jsonify({'success': True, 'receipt': receipt.to_dict()})
        
    except Exception as e:
        db.session.rollback()
        return jsonify({'error': f'Purchase failed: {str(e)}'}), 500
    


# ==================== DASHBOARD STATS ====================

@app.route('/api/english/dashboard/stats', methods=['GET'])
@login_required
def get_english_dashboard_stats():
    if 'Director' in [role.name for role in current_user.english_roles]:
        stats = {
            'total_users': User.query.count(),
            'total_students': EnglishStudent.query.count(),
            'total_books': EnglishBook.query.count(),
            'total_receipts': BookPurchaseReceipt.query.count() + EnglishRegistrationReceipt.query.count(),
            'recent_receipts': []
        }
    elif 'Cashier' in [role.name for role in current_user.english_roles]:
        stats = {
            'total_students': EnglishStudent.query.count(),
            'total_books_sold': sum(r.total_amount for r in BookPurchaseReceipt.query.filter_by(cashier_id=current_user.id)),
            'total_enrollments': EnglishRegistrationReceipt.query.filter_by(cashier_id=current_user.id).count()
        }
    else:
        stats = {}
    
    return jsonify(stats)

@app.route('/api/private/dashboard/stats', methods=['GET'])
@login_required
def get_private_dashboard_stats():
    user_roles = [role.name for role in current_user.private_roles]
    
    if 'Director' in user_roles or 'Cashier' in user_roles:
        stats = {
            'total_students': PrivateStudent.query.count(),
            'total_books': PrivateBook.query.count(),
            'total_receipts': PrivateEnrollmentReceipt.query.count() + PrivateBookReceipt.query.count() + PrivateUniformReceipt.query.count() + PrivateFormReceipt.query.count(),
        }
        
        if 'Director' in user_roles:
            stats['total_users'] = User.query.count()
    else:
        stats = {}
    
    return jsonify(stats)

# Delete user (works for both systems)
@app.route('/api/delete-user/<user_id>', methods=['DELETE'])
@login_required
@require_password_changed
@requires_roles('english', 'Director')
def delete_user(user_id):
    user = User.query.get(user_id)
    if not user:
        return jsonify({'error': 'User not found'}), 404

    # Prevent self-deletion
    if user.id == current_user.id:
        return jsonify({'error': 'You cannot delete your own account.'}), 400

    # Prevent deleting the last English Director
    is_english_director = any(role.name == 'Director' for role in user.english_roles)
    if is_english_director:
        total_english_directors = User.query.filter(User.english_roles.any(name='Director')).count()
        if total_english_directors <= 1:
            return jsonify({'error': 'Cannot delete the only English Director from the system'}), 400
    
    # Prevent deleting the last Private Director
    is_private_director = any(role.name == 'Director' for role in user.private_roles)
    if is_private_director:
        total_private_directors = User.query.filter(User.private_roles.any(name='Director')).count()
        if total_private_directors <= 1:
            return jsonify({'error': 'Cannot delete the only Private Director from the system'}), 400

    try:
        db.session.delete(user)
        db.session.commit()
        return jsonify({'success': True, 'message': 'User deleted successfully'})
    except Exception as e:
        db.session.rollback()
        return jsonify({'error': f'Failed to delete user: {str(e)}'}), 500


@app.route('/api/new-password/<token>', methods=["POST", "GET"])
def new_password(token):
    if request.method == "GET":
        link_entry = Link_Reset.query.filter_by(link=token).first()

        if not link_entry:
            return jsonify({"error": "Invalid or expired link. Click the button below to request another."}), 403
            
        # Check if the link is older than 30 minutes
        time_diff = datetime.utcnow() - link_entry.created_at
        if time_diff > timedelta(minutes=15):
            return jsonify({"error": "The confirmation link has expired. Click the button below to request another."}), 403
        
        return jsonify({"message": "You can enter new password below."}), 200

    else:
        password = request.form.get("new-password")
        password_conf = request.form.get("confirm-password")
        if password == '123456':
            return jsonify({'error': 'Cannot change back to default password.'}), 400
        if len(password) < 6:
            return jsonify({'error': 'Password must be at least 6 characters.'}), 400
        if password_conf == password:
            link_entry_post = Link_Reset.query.filter_by(link=token).first()
            user = User.query.filter_by(email=link_entry_post.email).first()
            user.set_password(password)            
            link_entry_post.link = str(uuid.uuid4())
            db.session.commit()
            session.clear()
            return jsonify({"messsage": "Password reset successful. Login now."}), 200
        else:
            return jsonify({"error": "Passwords do not match."}), 403


@app.post('/api/reset-password')
def reset_password():
    data = request.get_json()
    email = data.get('email').lower()

    user = User.query.filter_by(email=email).first()
    if not user:
        time.sleep(3.5)
        return jsonify({
            'message': f'We have sent a reset-password email if you have an account with us.'}), 200

    unique_uuid = str(uuid.uuid4())

    # Generate a unique token using itsdangerous
    token = s.dumps(email + str(unique_uuid), salt='password-reset')

    token += str(unique_uuid)
    # Combine the token with the UUID4 in the link
    verification_link = f"{BASE_URL_FRONTEND}/new-password/{token}"

    # Save link and timestamp in the database
    link_entry = Link_Reset(email=user.email, link=token)
    db.session.add(link_entry)
    db.session.commit()

    # Send email with the verification link
    msg = send_reset_email(email, verification_link)
    try:
        # mail.send(msg)
        res = send_mail_vercel(msg)
        if not res.get("status"):
            raise Exception(res.get("error"))
    except Exception as e:
        if str(e) == '[Errno 11001] getaddrinfo failed':
            error = 'Failed to connect. Please check your internet connection.'
        else:
            error = f'Error: {str(e)}'
        return jsonify({
            'error': f'{error}'}), 400
    return jsonify({
        'message': f'We have sent a reset-password email if you have an account with us.'}), 200






@app.route('/api/form-stock', methods=['GET'])
@login_required
@require_password_changed
@requires_roles('english', 'Cashier')
def get_form_stock():
    forms = FormStock.query.order_by(FormStock.created_at.desc()).all()
    return jsonify([{
        'id': f.id,
        'class_code': f.class_code,
        'class_name': f.class_name,
        'price': float(f.price),
        'stock_quantity': f.stock_quantity,
        'created_at': f.created_at.isoformat(),
        'updated_at': f.updated_at.isoformat()
    } for f in forms])

@app.route('/api/form-stock', methods=['POST'])
@login_required
@require_password_changed
@requires_roles('english', 'Cashier')
def create_form_stock():
    data = request.get_json()
    
    class_code = data.get('class_code')
    price = data.get('price')
    stock_quantity = data.get('stock_quantity')
    
    errors = {}
    
    if not class_code:
        errors['class_code'] = 'Class is required'
    else:
        # Check if form stock already exists for this class
        existing = FormStock.query.filter_by(class_code=class_code).first()
        if existing:
            errors['class_code'] = 'Form stock already exists for this class'
    
    try:
        price = float(price)
        if price <= 0:
            errors['price'] = 'Price must be greater than 0'
    except (TypeError, ValueError):
        errors['price'] = 'Invalid price'
    
    try:
        stock_quantity = int(stock_quantity)
        if stock_quantity < 0:
            errors['stock_quantity'] = 'Stock quantity cannot be negative'
    except (TypeError, ValueError):
        errors['stock_quantity'] = 'Invalid stock quantity'
    
    if errors:
        return jsonify({'success': False, 'errors': errors}), 400
    
    # Get class name from EnglishClass
    english_class = EnglishClass.query.filter_by(code=class_code).first()
    if not english_class:
        return jsonify({'error': 'Invalid class code'}), 400
    
    class_name = f"{english_class.code} - {english_class.name}"
    
    form_stock = FormStock(
        class_code=class_code,
        class_name=class_name,
        price=price,
        stock_quantity=stock_quantity
    )
    
    db.session.add(form_stock)
    db.session.commit()
    
    return jsonify({'success': True, 'form_stock_id': form_stock.id})

@app.route('/api/form-stock/<int:form_id>', methods=['PUT'])
@login_required
@require_password_changed
@requires_roles('english', 'Cashier')
def update_form_stock(form_id):
    form_stock = FormStock.query.get(form_id)
    if not form_stock:
        return jsonify({'error': 'Form stock not found'}), 404
    
    data = request.get_json()
    
    errors = {}
    
    price = data.get('price')
    stock_quantity = data.get('stock_quantity')
    
    try:
        price = float(price)
        if price <= 0:
            errors['price'] = 'Price must be greater than 0'
    except (TypeError, ValueError):
        errors['price'] = 'Invalid price'
    
    try:
        stock_quantity = int(stock_quantity)
        if stock_quantity < 0:
            errors['stock_quantity'] = 'Stock quantity cannot be negative'
    except (TypeError, ValueError):
        errors['stock_quantity'] = 'Invalid stock quantity'
    
    if errors:
        return jsonify({'success': False, 'errors': errors}), 400
    
    form_stock.price = price
    form_stock.stock_quantity = stock_quantity
    
    db.session.commit()
    
    return jsonify({'success': True})


@app.route('/api/english/students/<student_id>/level-books', methods=['GET'])
@login_required
@require_password_changed
@requires_roles('english', 'Cashier')
def get_english_student_level_books(student_id):
    student = EnglishStudent.query.get(student_id)
    if not student:
        return jsonify({'error': 'Student not found'}), 404
    
    student_status = request.args.get('student_status', 'fresh')
    
    # Get the English class to find its ID
    english_class = EnglishClass.query.filter_by(code=student.class_code).first()
    if not english_class:
        return jsonify({'error': 'Student class not found'}), 404
    
    # Get all books for this student's class_id and level from EnglishBook table
    books = EnglishBook.query.filter_by(
        class_id=english_class.id,
        class_type='english',
        level=str(student.level_order) if student.level_order else None,
        student_status=student_status
    ).all()
    
    return jsonify({
        'books': [{
            'id': book.id,
            'title': book.title,
            'author': book.author,
            'price': float(book.price),
            'quantity': book.quantity,
            'class_name': book.class_name,
            'class_type': book.class_type
        } for book in books]
    }), 200



@app.route('/api/english/sessions', methods=['GET'])
@login_required
def get_english_sessions():
    include_deleted = request.args.get('include_deleted', 'false').lower() == 'true'

    try:
        query = EnglishSession.query
        if not include_deleted:
            query = query.filter_by(deleted=False)
        sessions = query.order_by(EnglishSession.created_at.desc()).all()
        return jsonify([s.to_dict() for s in sessions]), 200
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/english/create-session', methods=['POST'])
@login_required
@require_password_changed
@requires_roles('english', 'InventoryManager')
def create_english_session():
    data = request.get_json()
    name = (data.get('name') or '').strip()
    time_text = (data.get('time') or '').strip()
    starting_str = data.get('starting')
    ending_str = data.get('ending')
    
    if not name:
        return jsonify({'error': 'Session name is required'}), 400
    
    existing = EnglishSession.query.filter_by(name=name).first()
    # if existing:
    #     return jsonify({'error': 'Session name already exists'}), 400
    
    session = EnglishSession(name=name)
    db.session.add(session)
    db.session.flush()  # get session.id

    if time_text:
        time_entry = EnglishSessionTime(session_id=session.id, time_text=time_text)
        db.session.add(time_entry)
    
    if starting_str and ending_str:
        try:
            starting = datetime.fromisoformat(starting_str)
            ending = datetime.fromisoformat(ending_str)
            if starting >= ending:
                return jsonify({'error': 'Starting must be before ending'}), 400
            period = EnglishSessionPeriod(session_id=session.id, starting=starting, ending=ending)
            db.session.add(period)
        except ValueError:
            return jsonify({'error': 'Invalid date format. Use ISO format (YYYY-MM-DDTHH:MM:SS)'}), 400

    db.session.commit()
    
    return jsonify({'success': True, 'session': session.to_dict()}), 201


@app.route('/api/english/update-session/<session_id>', methods=['PUT'])
@login_required
@require_password_changed
@requires_roles('english', 'InventoryManager')
def update_english_session(session_id):
    session = EnglishSession.query.get(session_id)
    if not session:
        return jsonify({'error': 'Session not found'}), 404
    
    data = request.get_json()
    name = (data.get('name') or '').strip()

    time_text = (data.get('time') or '').strip()
    starting_str = data.get('starting')
    ending_str = data.get('ending')
    
    if not name:
        return jsonify({'error': 'Session name is required'}), 400
    
    existing = EnglishSession.query.filter(EnglishSession.name == name, EnglishSession.id != session_id).first()
    if existing:
        return jsonify({'error': 'Session name already exists'}), 400
    
    if time_text is not None:
        time_text = time_text.strip()
        if time_text:
            if session.time_info:
                session.time_info.time_text = time_text
            else:
                session.time_info = EnglishSessionTime(session_id=session.id, time_text=time_text)
        # If time_text is empty string, we do NOT delete; we leave as is.
    
    # Update period if both starting and ending are provided (non-None)
    if starting_str is not None and ending_str is not None:
        # If both are empty strings, we do nothing (keep existing)
        if starting_str and ending_str:
            try:
                starting = datetime.fromisoformat(starting_str)
                ending = datetime.fromisoformat(ending_str)
                if starting >= ending:
                    return jsonify({'error': 'Starting must be before ending'}), 400
                if session.period:
                    session.period.starting = starting
                    session.period.ending = ending
                else:
                    session.period = EnglishSessionPeriod(session_id=session.id, starting=starting, ending=ending)
            except ValueError:
                return jsonify({'error': 'Invalid date format. Use ISO format (YYYY-MM-DDTHH:MM:SS)'}), 400
        # If both are empty strings, we skip (no deletion)

    session.name = name
    db.session.commit()
    
    return jsonify({'success': True, 'session': session.to_dict()}), 200


@app.route('/api/english/delete-session/<session_id>', methods=['DELETE'])
@login_required
@require_password_changed
@requires_roles('english', 'InventoryManager')
def delete_english_session(session_id):
    session = EnglishSession.query.get(session_id)
    if not session:
        return jsonify({'error': 'Session not found'}), 404
    
    session.deleted = True
    db.session.commit()
    
    return jsonify({'success': True, 'message': 'Session deleted'}), 200


# ==================== ANALYST ROUTES (English only) ====================

@app.route('/api/analyst/transactions', methods=['GET'])
@login_required
@require_password_changed
@requires_roles('english', 'Analyst')
def get_transactions():
    """Get all transactions with filtering - English system only."""
    start_date = request.args.get('start_date')
    end_date = request.args.get('end_date')
    payment_method = request.args.get('payment_method')
    transaction_type = request.args.get('transaction_type')  # form, registration, english, general
    min_amount = request.args.get('min_amount', type=float)
    max_amount = request.args.get('max_amount', type=float)
    cashier_id = request.args.get('cashier_id', type=str)
    session_id = request.args.get('session_id', type=str)   # NEW

    transactions = []

    # 1. English registration receipts – split form and registration fees with proportional discount
    for r in EnglishRegistrationReceipt.query.all():
        if session_id and str(r.session_id) != session_id:
            continue
        
        # Calculate subtotal (sum of both fees that are actually charged)
        subtotal = 0
        if r.charge_form_fee:
            subtotal += float(r.form_fee_amount)
        if r.charge_registration_fee:
            subtotal += float(r.registration_fee_amount)
        total_discount = float(r.discount_amount) if r.discount_amount else 0

        # Form fee transaction
        if r.charge_form_fee and r.form_fee_amount > 0:
            amount = float(r.form_fee_amount)
            item_discount = 0
            if total_discount > 0 and subtotal > 0:
                discount_ratio = amount / subtotal
                item_discount = total_discount * discount_ratio
            item_total = amount - item_discount
            
            session_obj = r.session
            session_data = {
                'session_id': str(session_obj.id) if session_obj else None,
                'session_name': session_obj.name if session_obj else None,
                'session_time_text': session_obj.time_info.time_text if session_obj and session_obj.time_info else None,
                'session_starting': session_obj.period.starting.isoformat() if session_obj and session_obj.period else None,
                'session_ending': session_obj.period.ending.isoformat() if session_obj and session_obj.period else None,
            }

            transactions.append({
                'id': f"{r.id}_form",
                'receipt_number': r.receipt_number,
                'date': r.created_at,
                'type': 'form',
                'student_name': r.student.name,
                'student_phone': r.student.phone,
                'student_email': r.student.email or '',
                'amount': item_total,
                'subtotal': amount,
                'discount_amount': item_discount,
                'discount_reason': r.discount_reason if r.discount_amount > 0 else None,
                'discount_percentage': (item_discount / amount * 100) if amount > 0 else 0,
                'payment_method': r.payment_method,
                'cashier_id': r.cashier_id,
                'cashier_name': r.cashier.name,
                'class_code': r.class_code,
                'level_name': r.level_name,
                'item_title': 'Form Fee',
                'quantity': 1,
                **session_data,
            })

        # Registration fee transaction
        if r.charge_registration_fee and r.registration_fee_amount > 0:
            amount = float(r.registration_fee_amount)
            item_discount = 0
            if total_discount > 0 and subtotal > 0:
                discount_ratio = amount / subtotal
                item_discount = total_discount * discount_ratio
            item_total = amount - item_discount
            
            session_obj = r.session
            session_data = {
                'session_id': str(session_obj.id) if session_obj else None,
                'session_name': session_obj.name if session_obj else None,
                'session_time_text': session_obj.time_info.time_text if session_obj and session_obj.time_info else None,
                'session_starting': session_obj.period.starting.isoformat() if session_obj and session_obj.period else None,
                'session_ending': session_obj.period.ending.isoformat() if session_obj and session_obj.period else None,
            }

            transactions.append({
                'id': f"{r.id}_reg",
                'receipt_number': r.receipt_number,
                'date': r.created_at,
                'type': 'registration',
                'student_name': r.student.name,
                'student_phone': r.student.phone,
                'student_email': r.student.email or '',
                'amount': item_total,
                'subtotal': amount,
                'discount_amount': item_discount,
                'discount_reason': r.discount_reason if r.discount_amount > 0 else None,
                'discount_percentage': (item_discount / amount * 100) if amount > 0 else 0,
                'payment_method': r.payment_method,
                'cashier_id': r.cashier_id,
                'cashier_name': r.cashier.name,
                'class_code': r.class_code,
                'level_name': r.level_name,
                'item_title': 'Registration Fee',
                'quantity': 1,
                 **session_data  # NEW
            })

    # 2. Book purchase receipts – each book item with proportional discount (unchanged)
    for r in BookPurchaseReceipt.query.all():
        if session_id and str(r.session_id) != session_id:
            continue
        if r.items and isinstance(r.items, list):
            subtotal = (r.total_amount or 0) + (r.discount_amount or 0)
            for item in r.items:
                class_type = item.get('class_type', 'general')
                transaction_type_value = f"book_{class_type}"
                item_subtotal = item.get('total', 0)
                item_discount = 0
                if r.discount_amount > 0 and subtotal > 0:
                    discount_ratio = item_subtotal / subtotal
                    item_discount = r.discount_amount * discount_ratio
                item_total = item_subtotal - item_discount
                
                session_obj = r.session
                session_data = {
                    'session_id': str(session_obj.id) if session_obj else None,
                    'session_name': session_obj.name if session_obj else None,
                    'session_time_text': session_obj.time_info.time_text if session_obj and session_obj.time_info else None,
                    'session_starting': session_obj.period.starting.isoformat() if session_obj and session_obj.period else None,
                    'session_ending': session_obj.period.ending.isoformat() if session_obj and session_obj.period else None,
                }

                transactions.append({
                    'id': f"{r.id}_{item.get('book_id')}",
                    'receipt_number': r.receipt_number,
                    'date': r.created_at,
                    'type': transaction_type_value,
                    'student_name': r.student.name,
                    'student_phone': r.student.phone,
                    'student_email': r.student.email or '',
                    'amount': item_total,
                    'subtotal': item_subtotal,
                    'discount_amount': item_discount,
                    'discount_reason': r.discount_reason,
                    'discount_percentage': (item_discount / item_subtotal * 100) if item_subtotal > 0 else 0,
                    'payment_method': r.payment_method,
                    'cashier_id': r.cashier_id,
                    'cashier_name': r.cashier.name,
                    'class_code': None,
                    'level_name': None,
                    'item_title': item.get('title'),
                    'quantity': item.get('quantity'),
                    **session_data  # NEW
                })

    # Apply filters (same as before)
    filtered = transactions
    if start_date:
        start = datetime.fromisoformat(start_date) if 'T' in start_date else datetime.fromisoformat(start_date + 'T00:00:00')
        filtered = [t for t in filtered if t['date'] >= start]
    if end_date:
        end = datetime.fromisoformat(end_date) if 'T' in end_date else datetime.fromisoformat(end_date + 'T23:59:59')
        filtered = [t for t in filtered if t['date'] <= end]
    if payment_method:
        filtered = [t for t in filtered if t['payment_method'] == payment_method]
    if transaction_type:
        filtered = [t for t in filtered if t['type'] == transaction_type]
    if min_amount is not None:
        filtered = [t for t in filtered if t['amount'] >= min_amount]
    if max_amount is not None:
        filtered = [t for t in filtered if t['amount'] <= max_amount]
    if cashier_id is not None:
        filtered = [t for t in filtered if t['cashier_id'] == cashier_id]

    filtered.sort(key=lambda x: x['date'], reverse=True)
    total_amount = sum(t['amount'] for t in filtered)
    total_discount = sum(t['discount_amount'] for t in filtered)

    return jsonify({
        'transactions': [{**t, 'date': t['date'].isoformat()} for t in filtered],
        'total_count': len(filtered),
        'total_amount': total_amount,
        'total_discount': total_discount
    })


@app.route('/api/analyst/students', methods=['GET'])
@login_required
@require_password_changed
@requires_roles('english', 'Analyst')
def get_analyst_students():
    """Get all English students with their enrollment and spending details."""
    class_filter = request.args.get('class_code')
    level_order = request.args.get('level_order', type=int)

    students = EnglishStudent.query.all()
    result = []

    for s in students:
        # Apply class filter
        if class_filter:
            if class_filter == 'not_enrolled' and s.class_code:
                continue
            elif class_filter != 'not_enrolled' and s.class_code != class_filter:
                continue

        # Apply level filter
        if level_order is not None and s.level_order != level_order:
            continue

        receipts = []
        total_spent = 0.0
        
        # Count actual receipts from the two receipt models
        registration_receipts_count = s.registration_receipts.count()
        book_receipts_count = s.book_receipts.count()
        receipts_count = registration_receipts_count + book_receipts_count

        # Build receipts array for display (splitting form/reg fees and book items for detailed view)
        # Registration receipts - split into form & reg for display
        for r in s.registration_receipts:
            # Form fee
            if r.charge_form_fee and r.form_fee_amount and float(r.form_fee_amount) > 0:
                amount = float(r.form_fee_amount)
                total_spent += amount
                receipts.append({
                    'receipt_number': r.receipt_number,
                    'date': r.created_at.isoformat(),
                    'type': 'form',
                    'amount': amount,
                    'payment_method': r.payment_method
                })
            # Registration fee
            if r.charge_registration_fee and r.registration_fee_amount and float(r.registration_fee_amount) > 0:
                amount = float(r.registration_fee_amount)
                total_spent += amount
                receipts.append({
                    'receipt_number': r.receipt_number,
                    'date': r.created_at.isoformat(),
                    'type': 'registration',
                    'amount': amount,
                    'payment_method': r.payment_method
                })

        # Book purchase receipts – each item as separate receipt entry for display
        for r in s.book_receipts:
            if r.items and isinstance(r.items, list):
                # Calculate total before discount for proportional distribution
                items_subtotal = sum(float(item.get('total', 0)) for item in r.items)
                total_discount = float(r.discount_amount) if r.discount_amount else 0
                
                for item in r.items:
                    item_subtotal = float(item.get('total', 0))
                    
                    # Calculate proportional discount for this item
                    item_discount = 0
                    if total_discount > 0 and items_subtotal > 0:
                        discount_ratio = item_subtotal / items_subtotal
                        item_discount = total_discount * discount_ratio
                    
                    item_total = item_subtotal - item_discount
                    
                    if item_total > 0:
                        total_spent += item_total
                        receipts.append({
                            'receipt_number': r.receipt_number,
                            'date': r.created_at.isoformat(),
                            'type': f"book_{item.get('class_type', 'general')}",
                            'amount': item_total,
                            'payment_method': r.payment_method
                        })
            else:
                # Fallback for old receipts without items
                amount = float(r.total_amount) if r.total_amount else 0
                if amount > 0:
                    total_spent += amount
                    receipts.append({
                        'receipt_number': r.receipt_number,
                        'date': r.created_at.isoformat(),
                        'type': 'book_purchase',
                        'amount': amount,
                        'payment_method': r.payment_method
                    })

        receipts.sort(key=lambda x: x['date'], reverse=True)

        result.append({
            'id': s.id,
            'name': s.name,
            'phone': s.phone,
            'email': s.email or '',
            'student_type': 'english',
            'class_code': s.class_code or 'Not Enrolled',
            'level_name': s.level_name or '',
            'level_order': s.level_order,
            'total_spent': total_spent,
            'receipts_count': receipts_count,  # Now counts actual receipts, not split items
            'receipts': receipts,  # Still shows split items for detailed view
            'created_at': s.created_at.isoformat()
        })

    # Apply class filter again
    if class_filter:
        if class_filter == 'not_enrolled':
            result = [s for s in result if s['class_code'] == 'Not Enrolled']
        elif class_filter != 'all':
            result = [s for s in result if s['class_code'] == class_filter]

    result.sort(key=lambda x: x['name'])
    return jsonify(result)

@app.route('/api/analyst/summary', methods=['GET'])
@login_required
@require_password_changed
@requires_roles('english', 'Analyst')
def get_analyst_summary():
    """Get summary statistics for the analyst dashboard."""
    start_date = request.args.get('start_date')
    end_date = request.args.get('end_date')

    all_items = []

    # Registration receipts – split form/reg with proportional discount
    for r in EnglishRegistrationReceipt.query.all():
        # Determine subtotal for this receipt (form_fee + registration_fee)
        subtotal = float(r.form_fee_amount) + float(r.registration_fee_amount)
        total_discount = float(r.discount_amount) if r.discount_amount else 0

        # Form fee
        if r.charge_form_fee and r.form_fee_amount > 0:
            form_amount = float(r.form_fee_amount)
            form_discount = 0
            if total_discount > 0 and subtotal > 0:
                discount_ratio = form_amount / subtotal
                form_discount = total_discount * discount_ratio
            form_total = form_amount - form_discount
            all_items.append({
                'date': r.created_at,
                'amount': form_total,
                'type': 'form',
                'payment_method': r.payment_method
            })

        # Registration fee
        if r.charge_registration_fee and r.registration_fee_amount > 0:
            reg_amount = float(r.registration_fee_amount)
            reg_discount = 0
            if total_discount > 0 and subtotal > 0:
                discount_ratio = reg_amount / subtotal
                reg_discount = total_discount * discount_ratio
            reg_total = reg_amount - reg_discount
            all_items.append({
                'date': r.created_at,
                'amount': reg_total,
                'type': 'registration',
                'payment_method': r.payment_method
            })

    # Book receipts – each item with discount applied (unchanged – already correct)
    for r in BookPurchaseReceipt.query.all():
        if r.items and isinstance(r.items, list):
            items_subtotal = sum(float(item.get('total', 0)) for item in r.items)
            total_discount = float(r.discount_amount) if r.discount_amount else 0
            for item in r.items:
                class_type = item.get('class_type', 'general')
                item_subtotal = float(item.get('total', 0))
                item_discount = 0
                if total_discount > 0 and items_subtotal > 0:
                    discount_ratio = item_subtotal / items_subtotal
                    item_discount = total_discount * discount_ratio
                item_total = item_subtotal - item_discount
                if item_total > 0:
                    all_items.append({
                        'date': r.created_at,
                        'amount': item_total,
                        'type': f"book_{class_type}",
                        'payment_method': r.payment_method
                    })
        else:
            amount = float(r.total_amount) if r.total_amount else 0
            if amount > 0:
                all_items.append({
                    'date': r.created_at,
                    'amount': amount,
                    'type': 'book_purchase',
                    'payment_method': r.payment_method
                })

    # Apply date filters
    if start_date:
        start = datetime.fromisoformat(start_date) if 'T' in start_date else datetime.fromisoformat(start_date + 'T00:00:00')
        all_items = [i for i in all_items if i['date'] >= start]
    if end_date:
        end = datetime.fromisoformat(end_date) if 'T' in end_date else datetime.fromisoformat(end_date + 'T23:59:59')
        all_items = [i for i in all_items if i['date'] <= end]

    total_revenue = sum(i['amount'] for i in all_items)
    total_transactions = len(all_items)

    by_type = {}
    by_payment = {}
    for i in all_items:
        by_type[i['type']] = by_type.get(i['type'], 0) + i['amount']
        by_payment[i['payment_method']] = by_payment.get(i['payment_method'], 0) + i['amount']

    enrolled_students = EnglishStudent.query.filter(EnglishStudent.class_code.isnot(None)).count()
    total_students = EnglishStudent.query.count()

    return jsonify({
        'total_revenue': round(total_revenue, 2),
        'total_transactions': total_transactions,
        'form_revenue': round(by_type.get('form', 0), 2),
        'registration_revenue': round(by_type.get('registration', 0), 2),
        'books_revenue': round(by_type.get('book_english', 0) + by_type.get('book_general', 0) + by_type.get('book_purchase', 0), 2),
        'english_books_revenue': round(by_type.get('book_english', 0), 2),
        'general_books_revenue': round(by_type.get('book_general', 0), 2),
        'by_type': {k: round(v, 2) for k, v in by_type.items()},
        'by_payment': {k: round(v, 2) for k, v in by_payment.items()},
        'enrolled_students': enrolled_students,
        'total_students': total_students
    })

@app.route('/api/analyst/cashiers', methods=['GET'])
@login_required
@require_password_changed
@requires_roles('english', 'Analyst')
def get_cashiers():
    """Get list of users with Cashier role in English system."""
    # Assuming EnglishRole table has a 'Cashier' role
    cashier_role = EnglishRole.query.filter_by(name='Cashier').first()
    if not cashier_role:
        return jsonify([])
    cashiers = cashier_role.users.all()  # backref 'users' from EnglishRole
    return jsonify([{'id': u.id, 'name': u.name} for u in cashiers])


# Export endpoints (simplified)
@app.route('/api/analyst/export', methods=['POST'])
@login_required
@require_password_changed
@requires_roles('english', 'Analyst')
def export_data():
    """Export full data (unfiltered) to Excel."""
    data = request.get_json() or {}
    export_type = data.get('type')  # 'transactions' or 'students'
    return _build_export(export_type, filters={})


@app.route('/api/analyst/export-filtered', methods=['POST'])
@login_required
@require_password_changed
@requires_roles('english', 'Analyst')
def export_filtered_data():
    """Export filtered data to Excel."""
    data = request.get_json() or {}
    export_type = data.get('type')
    filters = data.get('filters', {})
    return _build_export(export_type, filters)


def _build_export(export_type, filters):
    """Helper to build Excel export for transactions or students."""

    wb = Workbook()
    wb.remove(wb.active)
    
    header_font = Font(bold=True, size=11)
    title_font = Font(bold=True, size=14)
    label_font = Font(bold=True, size=11)
    value_font = Font(size=11)
    
    def format_phone(phone):
        return str(phone) if phone else 'N/A'

    def safe_date(date_str, end=False):
        if not date_str:
            return None
        try:
            return datetime.fromisoformat(date_str + ('T23:59:59' if end else ''))
        except:
            return None

    def auto_width(ws, max_width=40):
        """Auto width that skips merged cells"""
        for col in ws.columns:
            max_length = 0
            col_letter = None
            for cell in col:
                if isinstance(cell, openpyxl.cell.cell.MergedCell):
                    continue
                if col_letter is None:
                    col_letter = cell.column_letter
                try:
                    if cell.value:
                        max_length = max(max_length, len(str(cell.value)))
                except:
                    pass
            if col_letter:
                ws.column_dimensions[col_letter].width = min(max_length + 2, max_width)

    def format_currency_cells(ws, currency_columns, start_row=2):
        """Apply currency formatting with commas, no decimals"""
        for row in ws.iter_rows(min_row=start_row):
            for cell in row:
                if cell.column in currency_columns and isinstance(cell.value, (int, float)):
                    cell.number_format = '#,##0'

    # Define which filters are relevant for each export type
    transaction_filters = ['start_date', 'end_date', 'payment_method', 'transaction_type', 'cashier_id', 'min_amount', 'max_amount', 'search_query']
    student_filters = ['class_code', 'level_order', 'search_query']
    
    # Check if filters are provided (only check relevant filters based on export_type)
    if export_type == 'transactions':
        has_filters = any([
            filters.get('start_date'),
            filters.get('end_date'),
            filters.get('payment_method'),
            filters.get('transaction_type'),
            filters.get('cashier_id'),
            filters.get('min_amount'),
            filters.get('max_amount'),
            filters.get('search_query'),
            filters.get('session_id')  
        ])
    else:  # students
        has_filters = any([
            filters.get('class_code'),
            filters.get('level_order'),
            filters.get('search_query')
        ])

    # ============ SHEET 1: SUMMARY ============
    summary_ws = wb.create_sheet("Summary")
    
    # Title
    title_cell = summary_ws['A1']
    title_cell.value = f"{'FILTERED' if has_filters else 'ALL'} {export_type.upper()} EXPORT - SUMMARY"
    title_cell.font = title_font
    summary_ws.merge_cells('A1:D1')
    
    # Export date
    summary_ws['A3'] = "Export Date:"
    summary_ws['B3'] = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    summary_ws['A3'].font = label_font
    summary_ws['B3'].font = value_font
    
    row = 5
    
    # Summary statistics header
    summary_ws[f'A{row}'] = "SUMMARY STATISTICS"
    summary_ws[f'A{row}'].font = Font(bold=True, size=12)
    row += 2
    
    # Store the row numbers for later update
    if export_type == 'transactions':
        revenue_row = row
        summary_ws[f'A{revenue_row}'] = "Total Revenue:"
        summary_ws[f'B{revenue_row}'] = "Pending calculation"
        summary_ws[f'A{revenue_row}'].font = label_font
        row += 1
        
        transactions_row = row
        summary_ws[f'A{transactions_row}'] = "Total Transactions:"
        summary_ws[f'B{transactions_row}'] = "Pending calculation"
        summary_ws[f'A{transactions_row}'].font = label_font
        row += 1
    else:
        students_row = row
        summary_ws[f'A{students_row}'] = "Total Students:"
        summary_ws[f'B{students_row}'] = "Pending calculation"
        summary_ws[f'A{students_row}'].font = label_font
        row += 1
        
        spent_row = row
        summary_ws[f'A{spent_row}'] = "Total Spent:"
        summary_ws[f'B{spent_row}'] = "Pending calculation"
        summary_ws[f'A{spent_row}'].font = label_font
        row += 1
    
    row += 1
    
    # Filters applied section - ONLY show relevant filters for this export type
    if has_filters:
        summary_ws[f'A{row}'] = "FILTERS APPLIED"
        summary_ws[f'A{row}'].font = Font(bold=True, size=12)
        row += 2
        
        if export_type == 'transactions':
            # Show only transaction filters
            if filters.get('start_date'):
                summary_ws[f'A{row}'] = "Start Date:"
                summary_ws[f'B{row}'] = filters.get('start_date')
                summary_ws[f'A{row}'].font = label_font
                row += 1
            if filters.get('end_date'):
                summary_ws[f'A{row}'] = "End Date:"
                summary_ws[f'B{row}'] = filters.get('end_date')
                summary_ws[f'A{row}'].font = label_font
                row += 1
            if filters.get('payment_method'):
                summary_ws[f'A{row}'] = "Payment Method:"
                summary_ws[f'B{row}'] = filters.get('payment_method')
                summary_ws[f'A{row}'].font = label_font
                row += 1
            if filters.get('transaction_type'):
                summary_ws[f'A{row}'] = "Transaction Type:"
                summary_ws[f'B{row}'] = filters.get('transaction_type')
                summary_ws[f'A{row}'].font = label_font
                row += 1
            if filters.get('cashier_id'):
                summary_ws[f'A{row}'] = "Cashier ID:"
                summary_ws[f'B{row}'] = filters.get('cashier_id')
                summary_ws[f'A{row}'].font = label_font
                row += 1
            if filters.get('session_id'):
                # Get session object to show name + time
                session_obj = EnglishSession.query.get(filters.get('session_id'))
                session_label = session_obj.name
                if session_obj and session_obj.time_info and session_obj.time_info.time_text:
                    session_label += f' • {session_obj.time_info.time_text}'
                summary_ws[f'A{row}'] = "Session:"
                summary_ws[f'B{row}'] = session_label
                summary_ws[f'A{row}'].font = label_font
                row += 1
            if filters.get('min_amount'):
                summary_ws[f'A{row}'] = "Min Amount:"
                summary_ws[f'B{row}'] = f"₦{float(filters.get('min_amount')):,.0f}"
                summary_ws[f'A{row}'].font = label_font
                row += 1
            if filters.get('max_amount'):
                summary_ws[f'A{row}'] = "Max Amount:"
                summary_ws[f'B{row}'] = f"₦{float(filters.get('max_amount')):,.0f}"
                summary_ws[f'A{row}'].font = label_font
                row += 1
            if filters.get('search_query'):
                summary_ws[f'A{row}'] = "Search Query:"
                summary_ws[f'B{row}'] = filters.get('search_query')
                summary_ws[f'A{row}'].font = label_font
                row += 1
        else:  # students
            # Show only student filters
            if filters.get('class_code'):
                class_val = filters.get('class_code')
                display_class = "Not Enrolled" if class_val == 'not_enrolled' else class_val
                summary_ws[f'A{row}'] = "Class:"
                summary_ws[f'B{row}'] = display_class
                summary_ws[f'A{row}'].font = label_font
                row += 1
            if filters.get('level_order'):
                summary_ws[f'A{row}'] = "Level:"
                summary_ws[f'B{row}'] = f"Level {filters.get('level_order')}"
                summary_ws[f'A{row}'].font = label_font
                row += 1
            if filters.get('search_query'):
                summary_ws[f'A{row}'] = "Search Query:"
                summary_ws[f'B{row}'] = filters.get('search_query')
                summary_ws[f'A{row}'].font = label_font
                row += 1
    
    # Set column widths for summary sheet
    summary_ws.column_dimensions['A'].width = 20
    summary_ws.column_dimensions['B'].width = 30
    summary_ws.column_dimensions['C'].width = 15
    summary_ws.column_dimensions['D'].width = 15

    # ============ SHEET 2: DATA ============
    if export_type == 'transactions':
        start_dt = safe_date(filters.get('start_date'))
        end_dt = safe_date(filters.get('end_date'), end=True)
        payment_method = filters.get('payment_method')
        transaction_type_filter = filters.get('transaction_type')
        min_amount = filters.get('min_amount')
        max_amount = filters.get('max_amount')
        cashier_id = filters.get('cashier_id')
        session_id = filters.get('session_id')
        search_query_raw = filters.get('search_query')
        search_query = search_query_raw.lower() if search_query_raw else ''

        def match_date(d):
            return (not start_dt or d >= start_dt) and (not end_dt or d <= end_dt)

        def match_search(text):
            if not search_query:
                return True
            return search_query in str(text).lower()

        rows = []
        total_revenue = 0

        # Registration receipts
        for r in EnglishRegistrationReceipt.query.all():
            if session_id and str(r.session_id) != session_id:
                continue
            if not match_date(r.created_at):
                continue
            if payment_method and r.payment_method != payment_method:
                continue
            if cashier_id and r.cashier_id != cashier_id:
                continue
            
            # Get session object and formatted label
            session_obj = r.session
            session_label = format_session_label(session_obj) if session_obj else ''
            session_starting = session_obj.period.starting.strftime('%Y-%m-%d %H:%M') if session_obj and session_obj.period else ''
            session_ending = session_obj.period.ending.strftime('%Y-%m-%d %H:%M') if session_obj and session_obj.period else ''
            
            # Form fee
            if r.charge_form_fee and r.form_fee_amount > 0:
                if transaction_type_filter and transaction_type_filter != 'form':
                    pass
                else:
                    amount = float(r.form_fee_amount)
                    if (min_amount is not None and amount < min_amount) or (max_amount is not None and amount > max_amount):
                        pass
                    elif match_search(r.receipt_number) or match_search(r.student.name):
                        rows.append([
                            r.receipt_number, r.created_at.strftime('%Y-%m-%d %H:%M'), 'form',
                            r.student.name, format_phone(r.student.phone), r.student.email or '-',
                            'Form Fee', 1, amount, 0, amount, '-', r.payment_method, r.cashier.name,
                            session_label, session_starting, session_ending  
                        ])
                        total_revenue += amount
            # Registration fee
            if r.charge_registration_fee and r.registration_fee_amount > 0:
                if transaction_type_filter and transaction_type_filter != 'registration':
                    pass
                else:
                    amount = float(r.registration_fee_amount)
                    if (min_amount is not None and amount < min_amount) or (max_amount is not None and amount > max_amount):
                        pass
                    elif match_search(r.receipt_number) or match_search(r.student.name):
                        rows.append([
                            r.receipt_number, r.created_at.strftime('%Y-%m-%d %H:%M'), 'registration',
                            r.student.name, format_phone(r.student.phone), r.student.email or '-',
                            'Registration Fee', 1, amount, 0, amount, '-', r.payment_method, r.cashier.name,
                            session_label, session_starting, session_ending
                        ])
                        total_revenue += amount

        # Book receipts
        for r in BookPurchaseReceipt.query.all():
            if session_id and str(r.session_id) != session_id:
                continue
            if not match_date(r.created_at):
                continue
            if payment_method and r.payment_method != payment_method:
                continue
            if cashier_id and r.cashier_id != cashier_id:
                continue
            
            # Get session object and formatted label
            session_obj = r.session
            session_label = format_session_label(session_obj) if session_obj else ''
            session_starting = session_obj.period.starting.strftime('%Y-%m-%d %H:%M') if session_obj and session_obj.period else ''
            session_ending = session_obj.period.ending.strftime('%Y-%m-%d %H:%M') if session_obj and session_obj.period else ''
            
            if r.items:
                subtotal = float(r.total_amount or 0) + float(r.discount_amount or 0)
                for item in r.items:
                    class_type = item.get('class_type', 'general')
                    transaction_type_value = f"book_{class_type}"
                    
                    if transaction_type_filter and transaction_type_filter != transaction_type_value:
                        continue
                    item_subtotal = float(item.get('total', 0))
                    if (min_amount is not None and item_subtotal < min_amount) or (max_amount is not None and item_subtotal > max_amount):
                        continue
                    item_discount = 0
                    if float(r.discount_amount or 0) > 0 and subtotal > 0:
                        discount_ratio = item_subtotal / subtotal
                        item_discount = float(r.discount_amount) * discount_ratio
                    item_total = item_subtotal - item_discount
                    if match_search(r.receipt_number) or match_search(r.student.name) or match_search(item.get('title')):
                        rows.append([
                            r.receipt_number, r.created_at.strftime('%Y-%m-%d %H:%M'), transaction_type_value,
                            r.student.name, format_phone(r.student.phone), r.student.email or '-',
                            item.get('title'), item.get('quantity'), item_subtotal, item_discount, item_total,
                            r.discount_reason or '-', r.payment_method, r.cashier.name,
                            session_label, session_starting, session_ending
                        ])
                        total_revenue += item_total

        rows.sort(key=lambda x: x[1], reverse=True)
        
        # Update summary sheet with actual values
        summary_ws[f'B{revenue_row}'] = f"₦{total_revenue:,.0f}"
        summary_ws[f'B{transactions_row}'] = len(rows)
        
        # Create data sheet
        data_ws = wb.create_sheet("Transaction Data")
        
        headers = ['Receipt Number', 'Date', 'Transaction Type', 'Student Name', 'Phone', 'Email',
                   'Item Name', 'Quantity', 'Subtotal', 'Discount', 'Total',
                   'Discount Reason', 'Payment Method', 'Cashier',
                   'Session', 'Starting', 'Ending']
        data_ws.append(headers)
        for cell in data_ws[1]:
            cell.font = header_font
            cell.alignment = Alignment(horizontal="center", vertical="center")
        
        for row in rows:
            data_ws.append(row)
        
        format_currency_cells(data_ws, currency_columns=[9, 10, 11])
        auto_width(data_ws, 50)

    elif export_type == 'students':
        class_filter = filters.get('class_code')
        level_order = filters.get('level_order')
        search_query_raw = filters.get('search_query')
        search_query = search_query_raw.lower() if search_query_raw else ''
        
        rows = []
        students = EnglishStudent.query.all()
        total_students_count = 0
        total_spent_all = 0
        
        for s in students:
            # Apply class filter
            if class_filter:
                if class_filter == 'not_enrolled' and s.class_code:
                    continue
                elif class_filter != 'not_enrolled' and s.class_code != class_filter:
                    continue
            
            # Apply level filter
            if level_order is not None and s.level_order != level_order:
                continue
            
            # Apply search filter
            if search_query:
                if not (search_query in s.name.lower() or search_query in s.phone.lower() or (s.email and search_query in s.email.lower())):
                    continue
            
            # Calculate total spent and receipt count
            total_spent = 0
            receipt_count = s.registration_receipts.count() + s.book_receipts.count()
            
            for r in s.registration_receipts:
                if r.charge_form_fee:
                    total_spent += float(r.form_fee_amount) if r.form_fee_amount else 0
                if r.charge_registration_fee:
                    total_spent += float(r.registration_fee_amount) if r.registration_fee_amount else 0
            
            for r in s.book_receipts:
                if r.items:
                    subtotal = float(r.total_amount or 0) + float(r.discount_amount or 0)
                    total_discount = float(r.discount_amount or 0)
                    
                    for item in r.items:
                        item_subtotal = float(item.get('total', 0))
                        item_discount = 0
                        if total_discount > 0 and subtotal > 0:
                            discount_ratio = item_subtotal / subtotal
                            item_discount = total_discount * discount_ratio
                        total_spent += (item_subtotal - item_discount)
                else:
                    total_spent += float(r.total_amount or 0)
            
            rows.append({
                'name': s.name,
                'phone': format_phone(s.phone),
                'email': s.email or '-',
                'class_code': s.class_code or 'Not Enrolled',
                'level_name': s.level_name or '-',
                'created_at': s.created_at.strftime('%Y-%m-%d') if s.created_at else 'N/A',
                'total_spent': total_spent,
                'receipts_count': receipt_count
            })
            total_students_count += 1
            total_spent_all += total_spent
        
        # Update summary sheet with actual values
        summary_ws[f'B{students_row}'] = total_students_count
        summary_ws[f'B{spent_row}'] = f"₦{total_spent_all:,.0f}"
        
        # Create data sheet
        data_ws = wb.create_sheet("Student Data")
        
        headers = ['Name', 'Phone', 'Email', 'Class', 'Level', 'Registration Date', 'Total Spent', 'Receipts Count']
        data_ws.append(headers)
        for cell in data_ws[1]:
            cell.font = header_font
            cell.alignment = Alignment(horizontal="center", vertical="center")
        
        for row in rows:
            data_ws.append([row['name'], row['phone'], row['email'], row['class_code'], 
                          row['level_name'], row['created_at'], row['total_spent'], row['receipts_count']])
        
        format_currency_cells(data_ws, currency_columns=[7])
        auto_width(data_ws, 40)

    # Remove the default empty sheet if it exists
    if 'Sheet' in wb.sheetnames:
        del wb['Sheet']

    output = BytesIO()
    wb.save(output)
    output.seek(0)
    
    # Choose filename
    if has_filters:
        filename = f"{export_type}_filtered_export.xlsx"
    else:
        filename = f"{export_type}_full_export.xlsx"
    
    return Response(
        output.getvalue(),
        mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        headers={'Content-Disposition': f'attachment; filename={filename}'}
    )

# ==================== CASHIER MY ANALYSIS (English) ====================


@app.route('/api/english/cashier/transactions', methods=['GET'])
@login_required
@require_password_changed
@requires_roles('english', 'Cashier')
def get_english_cashier_transactions():
    """Get transactions for the logged-in cashier with filtering and proportional discount."""
    start_date = request.args.get('start_date')
    end_date = request.args.get('end_date')
    payment_method = request.args.get('payment_method')
    transaction_type = request.args.get('transaction_type')
    min_amount = request.args.get('min_amount', type=float)
    max_amount = request.args.get('max_amount', type=float)
    session_id = request.args.get('session_id', type=str)

    all_transactions = []

    # Registration receipts – split form & registration with proportional discount
    for r in EnglishRegistrationReceipt.query.filter_by(cashier_id=current_user.id).all():
        if session_id and str(r.session_id) != session_id:
            continue
        # Date filtering
        if start_date:
            start = datetime.fromisoformat(start_date) if 'T' in start_date else datetime.fromisoformat(start_date + 'T00:00:00')
            if r.created_at < start:
                continue
        if end_date:
            end = datetime.fromisoformat(end_date) if 'T' in end_date else datetime.fromisoformat(end_date + 'T23:59:59')
            if r.created_at > end:
                continue
        if payment_method and r.payment_method != payment_method:
            continue

        # Calculate subtotal (sum of charged fees) and total discount
        subtotal = 0
        if r.charge_form_fee:
            subtotal += float(r.form_fee_amount)
        if r.charge_registration_fee:
            subtotal += float(r.registration_fee_amount)
        total_discount = float(r.discount_amount) if r.discount_amount else 0
        
        # Get session info - NEW
        session_obj = r.session
        session_data = {
            'session_id': str(session_obj.id) if session_obj else None,
            'session_name': session_obj.name if session_obj else None,
            'session_time_text': session_obj.time_info.time_text if session_obj and session_obj.time_info else None,
            'session_starting': session_obj.period.starting.isoformat() if session_obj and session_obj.period else None,
            'session_ending': session_obj.period.ending.isoformat() if session_obj and session_obj.period else None,
        }

        # Form fee
        if r.charge_form_fee and r.form_fee_amount > 0:
            if transaction_type and transaction_type != 'form':
                pass  # skip unless filter matches
            else:
                amount = float(r.form_fee_amount)
                item_discount = 0
                if total_discount > 0 and subtotal > 0:
                    discount_ratio = amount / subtotal
                    item_discount = total_discount * discount_ratio
                item_total = amount - item_discount
                if (min_amount is not None and item_total < min_amount) or (max_amount is not None and item_total > max_amount):
                    pass
                else:
                    all_transactions.append({
                        'id': f"{r.id}_form",
                        'receipt_number': r.receipt_number,
                        'date': r.created_at,
                        'type': 'form',
                        'student_name': r.student.name,
                        'student_phone': r.student.phone,
                        'student_email': r.student.email or '',
                        'amount': item_total,
                        'subtotal': amount,
                        'discount_amount': item_discount,
                        'discount_reason': r.discount_reason if r.discount_amount else None,
                        'discount_percentage': (item_discount / amount * 100) if amount > 0 else 0,
                        'payment_method': r.payment_method,
                        'cashier_id': r.cashier_id,
                        'cashier_name': r.cashier.name,
                        'class_code': r.class_code,
                        'level_name': r.level_name,
                        'item_title': 'Form Fee',
                        'quantity': 1,
                        **session_data   # NEW
                    })

        # Registration fee
        if r.charge_registration_fee and r.registration_fee_amount > 0:
            if transaction_type and transaction_type != 'registration':
                pass
            else:
                amount = float(r.registration_fee_amount)
                item_discount = 0
                if total_discount > 0 and subtotal > 0:
                    discount_ratio = amount / subtotal
                    item_discount = total_discount * discount_ratio
                item_total = amount - item_discount
                if (min_amount is not None and item_total < min_amount) or (max_amount is not None and item_total > max_amount):
                    pass
                else:
                    all_transactions.append({
                        'id': f"{r.id}_reg",
                        'receipt_number': r.receipt_number,
                        'date': r.created_at,
                        'type': 'registration',
                        'student_name': r.student.name,
                        'student_phone': r.student.phone,
                        'student_email': r.student.email or '',
                        'amount': item_total,
                        'subtotal': amount,
                        'discount_amount': item_discount,
                        'discount_reason': r.discount_reason if r.discount_amount else None,
                        'discount_percentage': (item_discount / amount * 100) if amount > 0 else 0,
                        'payment_method': r.payment_method,
                        'cashier_id': r.cashier_id,
                        'cashier_name': r.cashier.name,
                        'class_code': r.class_code,
                        'level_name': r.level_name,
                        'item_title': 'Registration Fee',
                        'quantity': 1,
                        **session_data   # NEW
                    })

    # Book purchase receipts (unchanged logic, already correct)
    for r in BookPurchaseReceipt.query.filter_by(cashier_id=current_user.id).all():
        if session_id and str(r.session_id) != session_id:
            continue
        if start_date:
            start = datetime.fromisoformat(start_date) if 'T' in start_date else datetime.fromisoformat(start_date + 'T00:00:00')
            if r.created_at < start:
                continue
        if end_date:
            end = datetime.fromisoformat(end_date) if 'T' in end_date else datetime.fromisoformat(end_date + 'T23:59:59')
            if r.created_at > end:
                continue
        if payment_method and r.payment_method != payment_method:
            continue
        
        # Get session info - NEW
        session_obj = r.session
        session_data = {
            'session_id': str(session_obj.id) if session_obj else None,
            'session_name': session_obj.name if session_obj else None,
            'session_time_text': session_obj.time_info.time_text if session_obj and session_obj.time_info else None,
            'session_starting': session_obj.period.starting.isoformat() if session_obj and session_obj.period else None,
            'session_ending': session_obj.period.ending.isoformat() if session_obj and session_obj.period else None,
        }

        if r.items:
            subtotal = float(r.total_amount or 0) + float(r.discount_amount or 0)
            for item in r.items:
                class_type = item.get('class_type', 'general')
                txn_type = f"book_{class_type}"
                if transaction_type and transaction_type != txn_type:
                    continue
                item_subtotal = float(item.get('total', 0))
                item_discount = 0
                if float(r.discount_amount or 0) > 0 and subtotal > 0:
                    discount_ratio = item_subtotal / subtotal
                    item_discount = float(r.discount_amount) * discount_ratio
                item_total = item_subtotal - item_discount
                if (min_amount is not None and item_total < min_amount) or (max_amount is not None and item_total > max_amount):
                    continue
                all_transactions.append({
                    'id': f"{r.id}_{item.get('book_id')}",
                    'receipt_number': r.receipt_number,
                    'date': r.created_at,
                    'type': txn_type,
                    'student_name': r.student.name,
                    'student_phone': r.student.phone,
                    'student_email': r.student.email or '',
                    'amount': item_total,
                    'subtotal': item_subtotal,
                    'discount_amount': item_discount,
                    'discount_reason': r.discount_reason,
                    'discount_percentage': (item_discount / item_subtotal * 100) if item_subtotal > 0 else 0,
                    'payment_method': r.payment_method,
                    'cashier_id': r.cashier_id,
                    'cashier_name': r.cashier.name,
                    'class_code': None,
                    'level_name': None,
                    'item_title': item.get('title'),
                    'quantity': item.get('quantity'),
                    **session_data   # NEW
                })
        else:
            amount = float(r.total_amount) if r.total_amount else 0
            txn_type = 'book_purchase'
            if transaction_type and transaction_type != txn_type:
                continue
            if (min_amount is not None and amount < min_amount) or (max_amount is not None and amount > max_amount):
                continue
            all_transactions.append({
                'id': r.id,
                'receipt_number': r.receipt_number,
                'date': r.created_at,
                'type': txn_type,
                'student_name': r.student.name,
                'student_phone': r.student.phone,
                'student_email': r.student.email or '',
                'amount': amount,
                'subtotal': amount,
                'discount_amount': 0,
                'discount_reason': r.discount_reason,
                'discount_percentage': 0,
                'payment_method': r.payment_method,
                'cashier_id': r.cashier_id,
                'cashier_name': r.cashier.name,
                'class_code': None,
                'level_name': None,
                'item_title': 'Book Purchase',
                'quantity': 1,
                **session_data   # NEW
            })

    # Apply filters (already done inline, but sort and return)
    all_transactions.sort(key=lambda x: x['date'], reverse=True)
    total_amount = sum(t['amount'] for t in all_transactions)
    total_discount = sum(t['discount_amount'] for t in all_transactions)

    return jsonify({
        'transactions': [{**t, 'date': t['date'].isoformat()} for t in all_transactions],
        'total_count': len(all_transactions),
        'total_amount': total_amount,
        'total_discount': total_discount
    })


@app.route('/api/english/cashier/summary', methods=['GET'])
@login_required
@require_password_changed
@requires_roles('english', 'Cashier')
def get_english_cashier_summary():
    """Summary for the logged-in cashier with proportional discount on registration fees."""

    start_date = request.args.get('start_date')
    end_date = request.args.get('end_date')
    payment_method = request.args.get('payment_method')
    transaction_type = request.args.get('transaction_type')
    min_amount = request.args.get('min_amount', type=float)
    max_amount = request.args.get('max_amount', type=float)

    now_local = datetime.now()
    today_start_local = datetime(now_local.year, now_local.month, now_local.day, 0, 0, 0)
    tomorrow_start_local = today_start_local + timedelta(days=1)

    all_items = []
    total_revenue = 0.0
    total_transactions = 0
    revenue_today = 0.0

    # Registration receipts – split form & registration with proportional discount
    for r in EnglishRegistrationReceipt.query.filter_by(cashier_id=current_user.id).all():
        # Apply date filters (using UTC, assuming server UTC)
        if start_date:
            start = datetime.fromisoformat(start_date) if 'T' in start_date else datetime.fromisoformat(start_date + 'T00:00:00')
            if r.created_at < start:
                continue
        if end_date:
            end = datetime.fromisoformat(end_date) if 'T' in end_date else datetime.fromisoformat(end_date + 'T23:59:59')
            if r.created_at > end:
                continue
        if payment_method and r.payment_method != payment_method:
            continue
        if transaction_type and transaction_type not in ['form', 'registration']:
            # skip if transaction_type filter is for books only
            continue

        date_utc = r.created_at
        date_local = date_utc + timedelta(hours=1)
        is_today_local = (today_start_local <= date_local < tomorrow_start_local)

        subtotal = 0
        if r.charge_form_fee:
            subtotal += float(r.form_fee_amount)
        if r.charge_registration_fee:
            subtotal += float(r.registration_fee_amount)
        total_discount = float(r.discount_amount) if r.discount_amount else 0

        # Form fee
        if r.charge_form_fee and r.form_fee_amount > 0:
            amount = float(r.form_fee_amount)
            item_discount = 0
            if total_discount > 0 and subtotal > 0:
                discount_ratio = amount / subtotal
                item_discount = total_discount * discount_ratio
            item_total = amount - item_discount

            if (min_amount is None or item_total >= min_amount) and (max_amount is None or item_total <= max_amount):
                all_items.append({
                    'date': date_utc,
                    'date_local': date_local,
                    'amount': item_total,
                    'type': 'form',
                    'payment_method': r.payment_method
                })
                total_revenue += item_total
                total_transactions += 1
                if is_today_local:
                    revenue_today += item_total

        # Registration fee
        if r.charge_registration_fee and r.registration_fee_amount > 0:
            amount = float(r.registration_fee_amount)
            item_discount = 0
            if total_discount > 0 and subtotal > 0:
                discount_ratio = amount / subtotal
                item_discount = total_discount * discount_ratio
            item_total = amount - item_discount

            if (min_amount is None or item_total >= min_amount) and (max_amount is None or item_total <= max_amount):
                all_items.append({
                    'date': date_utc,
                    'date_local': date_local,
                    'amount': item_total,
                    'type': 'registration',
                    'payment_method': r.payment_method
                })
                total_revenue += item_total
                total_transactions += 1
                if is_today_local:
                    revenue_today += item_total

    # Book receipts (already correct)
    for r in BookPurchaseReceipt.query.filter_by(cashier_id=current_user.id).all():
        if start_date:
            start = datetime.fromisoformat(start_date) if 'T' in start_date else datetime.fromisoformat(start_date + 'T00:00:00')
            if r.created_at < start:
                continue
        if end_date:
            end = datetime.fromisoformat(end_date) if 'T' in end_date else datetime.fromisoformat(end_date + 'T23:59:59')
            if r.created_at > end:
                continue
        if payment_method and r.payment_method != payment_method:
            continue

        date_utc = r.created_at
        date_local = date_utc + timedelta(hours=1)
        is_today_local = (today_start_local <= date_local < tomorrow_start_local)

        if r.items:
            items_subtotal = sum(float(item.get('total', 0)) for item in r.items)
            total_discount = float(r.discount_amount) if r.discount_amount else 0
            for item in r.items:
                class_type = item.get('class_type', 'general')
                txn_type = f"book_{class_type}"
                if transaction_type and transaction_type != txn_type:
                    continue
                item_subtotal = float(item.get('total', 0))
                item_discount = 0
                if total_discount > 0 and items_subtotal > 0:
                    discount_ratio = item_subtotal / items_subtotal
                    item_discount = total_discount * discount_ratio
                item_total = item_subtotal - item_discount
                if (min_amount is not None and item_total < min_amount) or (max_amount is not None and item_total > max_amount):
                    continue
                all_items.append({
                    'date': date_utc,
                    'date_local': date_local,
                    'amount': item_total,
                    'type': txn_type,
                    'payment_method': r.payment_method
                })
                total_revenue += item_total
                total_transactions += 1
                if is_today_local:
                    revenue_today += item_total
        else:
            amount = float(r.total_amount) if r.total_amount else 0
            txn_type = 'book_purchase'
            if transaction_type and transaction_type != txn_type:
                continue
            if (min_amount is not None and amount < min_amount) or (max_amount is not None and amount > max_amount):
                continue
            all_items.append({
                'date': date_utc,
                'date_local': date_local,
                'amount': amount,
                'type': txn_type,
                'payment_method': r.payment_method
            })
            total_revenue += amount
            total_transactions += 1
            if is_today_local:
                revenue_today += amount

    return jsonify({
        'total_revenue': total_revenue,
        'total_transactions': total_transactions,
        'revenue_today': revenue_today
    })



@app.route('/api/english/receipts', methods=['GET'])
@login_required
@require_password_changed
@requires_roles('english', 'Director')   # Allow Cashier, Analyst, Director etc.
def get_english_receipts():
    """Return combined registration and book purchase receipts with filtering."""
    # Get query parameters
    receipt_type = request.args.get('type')          # 'registration', 'book', or None for both
    start_date = request.args.get('start_date')
    end_date = request.args.get('end_date')
    student_name = request.args.get('student_name')
    receipt_number = request.args.get('receipt_number')
    payment_method = request.args.get('payment_method')
    cashier_id = request.args.get('cashier_id')

    # Helper to filter datetime
    def filter_date(dt):
        if start_date:
            start = datetime.fromisoformat(start_date) if 'T' in start_date else datetime.fromisoformat(start_date + 'T00:00:00')
            if dt < start:
                return False
        if end_date:
            end = datetime.fromisoformat(end_date) if 'T' in end_date else datetime.fromisoformat(end_date + 'T23:59:59')
            if dt > end:
                return False
        return True

    receipts = []

    # 1. Registration receipts
    if receipt_type is None or receipt_type == 'registration':
        query = EnglishRegistrationReceipt.query
        if cashier_id:
            query = query.filter_by(cashier_id=cashier_id)
        reg_receipts = query.all()
        for r in reg_receipts:
            if not filter_date(r.created_at):
                continue
            if payment_method and r.payment_method != payment_method:
                continue
            if receipt_number and receipt_number.lower() not in r.receipt_number.lower():
                continue
            if student_name and student_name.lower() not in r.student.name.lower():
                continue

            receipts.append({
                'id': r.id,
                'receipt_number': r.receipt_number,
                'type': 'registration',
                'student_id': r.student_id,
                'student_name': r.student.name,
                'student_phone': r.student.phone,
                'student_email': r.student.email or '',
                'date': r.created_at.isoformat(),
                'payment_method': r.payment_method,
                'cashier_name': r.cashier.name,
                'total_amount': float(r.total_amount),
                'discount_amount': float(r.discount_amount),
                'discount_reason': r.discount_reason,
                'details': {
                    'class_code': r.class_code,
                    'level_name': r.level_name,
                    'form_fee': float(r.form_fee_amount) if r.form_fee_amount else 0,
                    'registration_fee': float(r.registration_fee_amount) if r.registration_fee_amount else 0,
                    'charge_form_fee': r.charge_form_fee,
                    'charge_registration_fee': r.charge_registration_fee,
                    'receipt_type': r.receipt_type
                }
            })

    # 2. Book purchase receipts
    if receipt_type is None or receipt_type == 'book':
        book_receipts = BookPurchaseReceipt.query.all()
        for r in book_receipts:
            if not filter_date(r.created_at):
                continue
            if payment_method and r.payment_method != payment_method:
                continue
            if receipt_number and receipt_number.lower() not in r.receipt_number.lower():
                continue
            if student_name and student_name.lower() not in r.student.name.lower():
                continue

            receipts.append({
                'id': r.id,
                'receipt_number': r.receipt_number,
                'type': 'book',
                'student_id': r.student_id,
                'student_name': r.student.name,
                'student_phone': r.student.phone,
                'student_email': r.student.email or '',
                'date': r.created_at.isoformat(),
                'payment_method': r.payment_method,
                'cashier_name': r.cashier.name,
                'total_amount': float(r.total_amount),
                'discount_amount': float(r.discount_amount),
                'discount_reason': r.discount_reason,
                'details': {
                    'items': r.items,   # list of {title, quantity, price, total, class_name}
                }
            })

    # Sort by date descending
    receipts.sort(key=lambda x: x['date'], reverse=True)

    return jsonify(receipts)



@app.route('/api/english/cashier/receipts', methods=['GET'])
@login_required
@require_password_changed
@requires_roles('english', 'Cashier')
def get_english_cashier_receipts():
    """Return combined registration and book purchase receipts for the logged-in cashier."""
    receipt_type = request.args.get('type')
    start_date = request.args.get('start_date')
    end_date = request.args.get('end_date')
    student_name = request.args.get('student_name')
    receipt_number = request.args.get('receipt_number')
    payment_method = request.args.get('payment_method')

    def filter_date(dt):
        if start_date:
            start = datetime.fromisoformat(start_date) if 'T' in start_date else datetime.fromisoformat(start_date + 'T00:00:00')
            if dt < start:
                return False
        if end_date:
            end = datetime.fromisoformat(end_date) if 'T' in end_date else datetime.fromisoformat(end_date + 'T23:59:59')
            if dt > end:
                return False
        return True

    receipts = []

    # Registration receipts for this cashier
    if receipt_type is None or receipt_type == 'registration':
        reg_receipts = EnglishRegistrationReceipt.query.filter_by(cashier_id=current_user.id).all()
        for r in reg_receipts:
            if not filter_date(r.created_at):
                continue
            if payment_method and r.payment_method != payment_method:
                continue
            if receipt_number and receipt_number.lower() not in r.receipt_number.lower():
                continue
            if student_name and student_name.lower() not in r.student.name.lower():
                continue

            receipts.append({
                'id': r.id,
                'receipt_number': r.receipt_number,
                'type': 'registration',
                'student_id': r.student_id,
                'student_name': r.student.name,
                'student_phone': r.student.phone,
                'student_email': r.student.email or '',
                'date': r.created_at.isoformat(),
                'payment_method': r.payment_method,
                'cashier_name': r.cashier.name,
                'total_amount': float(r.total_amount),
                'discount_amount': float(r.discount_amount),
                'discount_reason': r.discount_reason,
                'details': {
                    'class_code': r.class_code,
                    'level_name': r.level_name,
                    'form_fee': float(r.form_fee_amount) if r.form_fee_amount else 0,
                    'registration_fee': float(r.registration_fee_amount) if r.registration_fee_amount else 0,
                    'charge_form_fee': r.charge_form_fee,
                    'charge_registration_fee': r.charge_registration_fee,
                    'receipt_type': r.receipt_type
                }
            })

    # Book purchase receipts for this cashier
    if receipt_type is None or receipt_type == 'book':
        book_receipts = BookPurchaseReceipt.query.filter_by(cashier_id=current_user.id).all()
        for r in book_receipts:
            if not filter_date(r.created_at):
                continue
            if payment_method and r.payment_method != payment_method:
                continue
            if receipt_number and receipt_number.lower() not in r.receipt_number.lower():
                continue
            if student_name and student_name.lower() not in r.student.name.lower():
                continue

            receipts.append({
                'id': r.id,
                'receipt_number': r.receipt_number,
                'type': 'book',
                'student_id': r.student_id,
                'student_name': r.student.name,
                'student_phone': r.student.phone,
                'student_email': r.student.email or '',
                'date': r.created_at.isoformat(),
                'payment_method': r.payment_method,
                'cashier_name': r.cashier.name,
                'total_amount': float(r.total_amount),
                'discount_amount': float(r.discount_amount),
                'discount_reason': r.discount_reason,
                'details': {
                    'items': r.items,
                }
            })

    receipts.sort(key=lambda x: x['date'], reverse=True)
    return jsonify(receipts)




# ==================== PRIVATE SCHOOL FEE MANAGEMENT ====================

@app.route('/api/private/fee-items', methods=['GET'])
@login_required
@require_password_changed
@requires_roles('private', 'Director')
def get_private_fee_items():
    include_linked = request.args.get('include_linked_to_uniforms_and_forms', 'true').lower() == 'true'

    query = PrivateFeeItem.query.order_by(PrivateFeeItem.created_at.desc())
    
    if not include_linked:
        # Subquery to get fee_item_id from forms and uniforms
        linked_form_ids = db.session.query(PrivateFormStock.fee_item_id).filter(PrivateFormStock.fee_item_id.isnot(None))
        linked_uniform_ids = db.session.query(PrivateUniform.fee_item_id).filter(PrivateUniform.fee_item_id.isnot(None))
        
        # Union of all linked IDs
        linked_ids = linked_form_ids.union(linked_uniform_ids).subquery()
        
        # Exclude fee items that appear in linked_ids
        query = query.filter(PrivateFeeItem.id.notin_(linked_ids))
    
    fee_items = query.all()
    
    result = []
    for item in fee_items:
        # Convert database codes to display names
        assigned_display = [private_school_code_to_display.get(a.class_code, a.class_code) for a in item.assignments]
        result.append({
            'id': item.id,
            'name': item.name,
            'amount': float(item.amount),
            'termly': item.termly,
            'assigned_classes': assigned_display,
            'created_at': item.created_at.isoformat(),
            'updated_at': item.updated_at.isoformat()
        })
    return jsonify(result)

@app.route('/api/private/fee-items', methods=['POST'])
@login_required
@require_password_changed
@requires_roles('private', 'Director')
def create_private_fee_item():
    data = request.get_json()
    name = (data.get('name') or '').strip()
    amount = data.get('amount')
    assigned_classes = data.get('assigned_classes', [])
    termly = data.get('termly')  # do NOT default – must be provided

    errors = {}
    if not name:
        errors['name'] = 'Fee name is required'
    try:
        amount = float(amount)
        if amount <= 0:
            errors['amount'] = 'Amount must be greater than 0'
    except (TypeError, ValueError):
        errors['amount'] = 'Invalid amount'

    if termly is None:
        errors['termly'] = 'Termly field is required'
    elif not isinstance(termly, bool):
        errors['termly'] = 'Termly must be true or false'
        
    if errors:
        return jsonify({'success': False, 'errors': errors}), 400

    fee_item = PrivateFeeItem(name=name, amount=amount, termly=termly)
    db.session.add(fee_item)
    db.session.flush()  # get ID

    for private_class in assigned_classes:
        class_code = private_school_classes_to_code[private_class]
        assignment = PrivateFeeAssignment(fee_item_id=fee_item.id, class_code=class_code)
        db.session.add(assignment)

    db.session.commit()
    return jsonify({'success': True, 'fee_item_id': fee_item.id})

@app.route('/api/private/fee-items/<fee_item_id>', methods=['PUT'])
@login_required
@require_password_changed
@requires_roles('private', 'Director')
def update_private_fee_item(fee_item_id):
    fee_item = PrivateFeeItem.query.get(fee_item_id)
    if not fee_item:
        return jsonify({'error': 'Fee item not found'}), 404

    data = request.get_json()
    name = (data.get('name') or '').strip()
    amount = data.get('amount')
    assigned_classes = data.get('assigned_classes', [])
    termly = data.get('termly')  # required!

    errors = {}
    if not name:
        errors['name'] = 'Fee name is required'
    try:
        amount = float(amount)
        if amount <= 0:
            errors['amount'] = 'Amount must be greater than 0'
    except (TypeError, ValueError):
        errors['amount'] = 'Invalid amount'

    if termly is None:
        errors['termly'] = 'Termly field is required'
    elif not isinstance(termly, bool):
        errors['termly'] = 'Termly must be true or false'
        
    if errors:
        return jsonify({'success': False, 'errors': errors}), 400

    fee_item.name = name
    fee_item.amount = amount
    fee_item.termly = termly

    # Update assignments: remove old, add new
    PrivateFeeAssignment.query.filter_by(fee_item_id=fee_item.id).delete()
    for private_class in assigned_classes:
        class_code = private_school_classes_to_code[private_class]
        assignment = PrivateFeeAssignment(fee_item_id=fee_item.id, class_code=class_code)
        db.session.add(assignment)

    db.session.commit()
    return jsonify({'success': True})

@app.route('/api/private/fee-items/<fee_item_id>', methods=['DELETE'])
@login_required
@require_password_changed
@requires_roles('private', 'Director')
def delete_private_fee_item(fee_item_id):
    fee_item = PrivateFeeItem.query.get(fee_item_id)
    if not fee_item:
        return jsonify({'error': 'Fee item not found'}), 404

    linked_form = PrivateFormStock.query.filter_by(fee_item_id=fee_item.id).first()
    if linked_form:
        return jsonify({'error': 'Cannot delete fee item because it is linked to a form stock. Delete the form first.'}), 400

    # Check if fee item is linked to a uniform
    linked_uniform = PrivateUniform.query.filter_by(fee_item_id=fee_item.id).first()
    if linked_uniform:
        return jsonify({'error': 'Cannot delete fee item because it is linked to a uniform. Delete the uniform first.'}), 400
    
    # Delete assignments first (cascade)
    PrivateFeeAssignment.query.filter_by(fee_item_id=fee_item.id).delete()
    db.session.delete(fee_item)
    db.session.commit()
    return jsonify({'success': True})

@app.route('/api/private/create-user', methods=['POST'])
@login_required
@require_password_changed
@requires_roles('private', 'Director')
def create_private_user():
    data = request.get_json()
    email = data.get('email').lower()
    name = data.get('name')
    
    if User.query.filter_by(email=email).first():
        return jsonify({'error': 'Email already exists.'}), 400
    
    user = User(
        name=name,
        email=email,
        must_change_password=True
    )
    user.set_password('123456')
    
    # Assign Private roles only
    role_names = data.get('roles', [])
    for role_name in role_names:
        role = PrivateRole.query.filter_by(name=role_name).first()
        if role:
            user.private_roles.append(role)
    
    msg = send_new_user_message(email, name)
    try:
        res = send_mail_vercel(msg)
        if not res.get("status"):
            raise Exception(res.get("error"))
    except Exception as e:
        if str(e) == '[Errno 11001] getaddrinfo failed':
            error = 'Failed to connect. Please check your internet connection.'
        else:
            error = f'Error: {str(e)}'
        return jsonify({'error': f'{error}'}), 400

    db.session.add(user)
    db.session.commit()
    
    return jsonify({'success': True, 'user_id': user.id})



# ==================== PRIVATE SCHOOL SESSION MANAGEMENT ====================

@app.route('/api/private/sessions', methods=['GET'])
@login_required
def get_private_sessions():
    include_deleted = request.args.get('include_deleted', 'false').lower() == 'true'
    query = PrivateSession.query
    if not include_deleted:
        query = query.filter_by(deleted=False)
    sessions = query.order_by(PrivateSession.created_at.desc()).all()
    return jsonify([s.to_dict() for s in sessions]), 200


@app.route('/api/private/sessions', methods=['POST'])
@login_required
@require_password_changed
@requires_roles('private', 'InventoryManager')
def create_private_session():
    data = request.get_json()
    name = (data.get('name') or '').strip()
    term = data.get('term')
    
    if not name:
        return jsonify({'error': 'Session name is required'}), 400
    if term not in [1, 2, 3]:
        return jsonify({'error': 'Term must be 1, 2, or 3'}), 400
    
    existing = PrivateSession.query.filter_by(name=name, term=term).first()
    if existing:
        return jsonify({'error': 'Session with this name and term already exists'}), 400
    
    next_index = get_next_color_index()
    color_letter = chr(ord('a') + next_index)

    session = PrivateSession(
        name=name,
        term=term,
        pass_color=color_letter
    )

    db.session.add(session)
    db.session.commit()
    
    # Initialize counter for this session+term
    # counter = PrivateReceiptCounter(session_id=session.id, term=term, last_number=0)
    # db.session.add(counter)
    # db.session.commit()
    
    return jsonify({'success': True, 'session': session.to_dict()}), 201


@app.route('/api/private/sessions/<session_id>', methods=['PUT'])
@login_required
@require_password_changed
@requires_roles('private', 'InventoryManager')
def update_private_session(session_id):
    session = PrivateSession.query.get(session_id)
    if not session:
        return jsonify({'error': 'Session not found'}), 404
    
    data = request.get_json()
    name = (data.get('name') or '').strip()
    term = data.get('term')
    
    if not name:
        return jsonify({'error': 'Session name is required'}), 400
    if term not in [1, 2, 3]:
        return jsonify({'error': 'Term must be 1, 2, or 3'}), 400
    
    existing = PrivateSession.query.filter(PrivateSession.name == name, PrivateSession.term == term, PrivateSession.id != session_id).first()
    if existing:
        return jsonify({'error': 'Session with this name and term already exists'}), 400
    
    session.name = name
    session.term = term
    db.session.commit()
    
    return jsonify({'success': True, 'session': session.to_dict()}), 200


@app.route('/api/private/sessions/<session_id>', methods=['DELETE'])
@login_required
@require_password_changed
@requires_roles('private', 'InventoryManager')
def delete_private_session(session_id):
    session = PrivateSession.query.get(session_id)
    if not session:
        return jsonify({'error': 'Session not found'}), 404
    
    session.deleted = True
    db.session.commit()
    return jsonify({'success': True}), 200


# ==================== PRIVATE SCHOOL FEES BY CLASS ====================

@app.route('/api/private/fees-by-class/<class_code>', methods=['GET'])
@login_required
def get_private_fees_by_class(class_code):
    # Parse query parameters, default True if not provided
    include_linked_to_form = request.args.get('include_linked_to_form', 'true').lower() == 'true'
    include_linked_to_uniform = request.args.get('include_linked_to_uniform', 'true').lower() == 'true'
    
    assignments = PrivateFeeAssignment.query.filter_by(class_code=class_code).all()
    
    result = []
    for assignment in assignments:
        item = assignment.fee_item
        if not item:
            continue
        
        # Determine if this fee item is linked to a form or uniform
        linked_to_form = PrivateFormStock.query.filter_by(fee_item_id=item.id).first() is not None
        linked_to_uniform = PrivateUniform.query.filter_by(fee_item_id=item.id).first() is not None
        
        # Skip if linked to form and we don't want to include form-linked fees
        if linked_to_form and not include_linked_to_form:
            continue
        # Skip if linked to uniform and we don't want to include uniform-linked fees
        if linked_to_uniform and not include_linked_to_uniform:
            continue
        
        result.append({
            'id': item.id,
            'name': item.name,
            'amount': float(item.amount),
            'termly': item.termly
        })
    
    return jsonify(result), 200


# ==================== PRIVATE SCHOOL ENROLLMENT ====================

@app.route('/api/private/enroll', methods=['POST'])
@login_required
@require_password_changed
@requires_roles('private', 'Cashier')
def enroll_private_student():
    data = request.get_json()
    
    # Either new student details or existing student ID
    student_id = data.get('student_id')

    name = (data.get('name') or '').strip()
    phone = (data.get('phone') or '').strip()
    email = (data.get('email') or '').strip()
    
    # Enrollment details
    session_id = data.get('session_id')
    class_code = data.get('class_code')
    selected_items = data.get('selected_items', [])  # list of {id, name, amount}
    payment_method = data.get('payment_method')
    discount_amount = data.get('discount_amount', 0)
    discount_reason = data.get('discount_reason', '')
    
    additional_amount = data.get('additional_amount', 0)
    additional_reason = data.get('additional_reason', '')
    
    # Validation
    if not session_id:
        return jsonify({'error': 'Session is required'}), 400
    if not class_code:
        return jsonify({'error': 'Class is required'}), 400
    if not selected_items:
        return jsonify({'error': 'At least one fee item must be selected'}), 400

    if discount_amount > 0 and additional_amount > 0:
        return jsonify({'error': 'Cannot apply both discount and additional fee. Choose one.'}), 400
    
    if discount_amount < 0 or additional_amount < 0:
        return jsonify({'error': 'Amounts cannot be negative'}), 400
    
    # Verify session exists and not deleted
    session = PrivateSession.query.filter_by(id=session_id, deleted=False).first()
    if not session:
        return jsonify({'error': 'Invalid or deleted session'}), 400
    
    # Verify class exists
    class_obj = PrivateSchoolClass.query.filter_by(code=class_code).first()
    if not class_obj:
        return jsonify({'error': 'Invalid class'}), 400
    
    # Calculate subtotal from selected items
    subtotal = sum(item['amount'] for item in selected_items)
    
    # Validate discount
    if discount_amount > subtotal:
        return jsonify({
            'error': f'Discount amount (₦{discount_amount:,.2f}) cannot exceed subtotal (₦{subtotal:,.2f})',
            'max_allowed_discount': subtotal
        }), 400
    
    discount_percentage = (discount_amount / subtotal * 100) if subtotal > 0 else 0
    if discount_percentage > 0 and not discount_reason:
        return jsonify({
            'error': f'Discount reason required.'
        }), 400
    
        # No validation for additional fee (can be any positive amount)
    if additional_amount > 0 and not additional_reason:
        return jsonify({'error': 'Additional fee reason required'}), 400
    
    total_amount = subtotal - discount_amount + additional_amount

    if student_id:
        # Existing student: verify, must have no class
        student = PrivateStudent.query.get(student_id)
        if not student:
            return jsonify({'error': 'Student not found'}), 404
        if student.class_code is not None:
            return jsonify({'error': 'Student already has a class. Use "Pay Fees" instead.'}), 400
        # Update student's class
        student.class_code = class_code
    else:
        if not name or not phone:
            return jsonify({'error': 'Name and phone are required for new student'}), 400
        if email and not is_valid_email(email):
            return jsonify({'error': 'Invalid email address'}), 400
    
        # Create student
        student = PrivateStudent(
            name=name,
            phone=phone,
            email=email if email else None,
            class_code=class_code
        )
        db.session.add(student)
        db.session.flush()
    
    # Generate receipt number
    # receipt_number = generate_private_receipt_number(session.id, session.term)

    receipt_number = generate_receipt_number()
    pass_number = get_next_pass_number(session.id)
    
    # Create receipt
    receipt = PrivateEnrollmentReceipt(
        receipt_number=receipt_number,
        student_id=student.id,
        session_id=session.id,
        term=session.term,
        class_code=class_code,
        items=selected_items,
        discount_amount=discount_amount,
        discount_reason=discount_reason,
        total_amount=total_amount,
        payment_method=payment_method,
        cashier_id=current_user.id,
        pass_number=pass_number,
        additional_amount=additional_amount,
        additional_reason=additional_reason,
    )
    db.session.add(receipt)
    db.session.commit()
    
    return jsonify({
        'success': True,
        'receipt': receipt.to_dict(),
        'student': student.to_dict()
    }), 200





# ==================== PRIVATE SCHOOL BOOK MANAGEMENT ====================

@app.route('/api/private/books', methods=['GET'])
@login_required
@require_password_changed
@requires_roles('private', 'InventoryManager')
def get_private_books():
    """Get all private school books."""
    books = PrivateBook.query.order_by(PrivateBook.created_at.desc()).all()
    return jsonify([b.to_dict() for b in books])


@app.route('/api/private/create-book', methods=['POST'])
@login_required
@require_password_changed
@requires_roles('private', 'InventoryManager')
def create_private_book():
    data = request.get_json()

    title = (data.get('title') or '').strip()
    author = (data.get('author') or '').strip()
    price = data.get('price')
    quantity = data.get('quantity')
    class_code = data.get('class_code')
    class_name = data.get('class_name') or ''

    errors = {}

    if not title:
        errors['title'] = 'Title is required'
    if not author:
        errors['author'] = 'Author is required'
    
    try:
        price = float(price)
        if price <= 0:
            errors['price'] = 'Price must be greater than 0'
    except (TypeError, ValueError):
        errors['price'] = 'Invalid price'
    
    try:
        quantity = int(quantity)
        if quantity < 0:
            errors['quantity'] = 'Quantity cannot be negative'
    except (TypeError, ValueError):
        errors['quantity'] = 'Invalid quantity'
    
    if not class_code:
        errors['class_code'] = 'Class is required'
    else:
        # Verify class exists
        class_obj = PrivateSchoolClass.query.filter_by(code=class_code).first()
        if not class_obj:
            errors['class_code'] = 'Invalid class selected'
        else:
            # Set class name if not provided
            if not class_name:
                class_name = class_obj.name

    if errors:
        return jsonify({'success': False, 'errors': errors}), 400

    book = PrivateBook(
        title=title,
        author=author,
        price=price,
        quantity=quantity,
        class_code=class_code,
        class_name=class_name
    )

    db.session.add(book)
    db.session.commit()

    return jsonify({'success': True, 'book_id': book.id})


@app.route('/api/private/update-book/<book_id>', methods=['PUT'])
@login_required
@require_password_changed
@requires_roles('private', 'InventoryManager')
def update_private_book(book_id):
    book = PrivateBook.query.get(book_id)
    if not book:
        return jsonify({'error': 'Book not found'}), 404
    
    data = request.get_json()

    title = (data.get('title') or '').strip()
    author = (data.get('author') or '').strip()
    price = data.get('price')
    quantity = data.get('quantity')
    class_code = data.get('class_code')
    class_name = data.get('class_name') or ''

    errors = {}

    if not title:
        errors['title'] = 'Title is required'
    if not author:
        errors['author'] = 'Author is required'
    
    try:
        price = float(price)
        if price <= 0:
            errors['price'] = 'Price must be greater than 0'
    except (TypeError, ValueError):
        errors['price'] = 'Invalid price'
    
    try:
        quantity = int(quantity)
        if quantity < 0:
            errors['quantity'] = 'Quantity cannot be negative'
    except (TypeError, ValueError):
        errors['quantity'] = 'Invalid quantity'
    
    if not class_code:
        errors['class_code'] = 'Class is required'
    else:
        class_obj = PrivateSchoolClass.query.filter_by(code=class_code).first()
        if not class_obj:
            errors['class_code'] = 'Invalid class selected'
        elif not class_name:
            class_name = class_obj.name

    if errors:
        return jsonify({'success': False, 'errors': errors}), 400

    # Check for actual changes (optional optimisation)
    has_changes = False
    if book.title != title:
        book.title = title
        has_changes = True
    if book.author != author:
        book.author = author
        has_changes = True
    if book.price != price:
        book.price = price
        has_changes = True
    if book.quantity != quantity:
        book.quantity = quantity
        has_changes = True
    if book.class_code != class_code:
        book.class_code = class_code
        has_changes = True
    if book.class_name != class_name:
        book.class_name = class_name
        has_changes = True

    if not has_changes:
        return jsonify({'success': False, 'message': 'No changes were made'}), 400

    db.session.commit()
    return jsonify({'success': True})

@app.route('/api/private/delete-book/<book_id>', methods=['DELETE'])
@login_required
@require_password_changed
@requires_roles('private', 'InventoryManager')
def delete_private_book(book_id):
    book = PrivateBook.query.get(book_id)
    if not book:
        return jsonify({'error': 'Book not found'}), 404

    db.session.delete(book)
    db.session.commit()

    return jsonify({'success': True, 'message': 'Book deleted successfully'}), 200


@app.route('/api/private/students', methods=['GET'])
@login_required
@require_password_changed
@requires_roles('private', 'Cashier')
def get_private_students():
    """Get all private school students (for selection)"""
    students = PrivateStudent.query.order_by(PrivateStudent.created_at.desc()).all()
    return jsonify([s.to_dict() for s in students])


@app.route('/api/private/search-students', methods=['GET'])
@login_required
@require_password_changed
@requires_roles('private', 'Cashier')
def search_private_students():
    """Search private students by name, phone, or email"""
    query = request.args.get('q', '')
    if not query:
        return jsonify([])
        
    students = get_student_by_search("private", query)
    
    return jsonify([s.to_dict() for s in students])


@app.route('/api/private/purchase-books', methods=['POST'])
@login_required
@require_password_changed
@requires_roles('private', 'Cashier')
def purchase_private_books():
    data = request.get_json()

    student_id = data.get('student_id')
    if not student_id:
        return jsonify({'error': 'Student ID is required'}), 400

    session_id = data.get('session_id')
    if not session_id:
        return jsonify({'error': 'Session ID is required'}), 400

    items = data.get('items', [])
    if not items or not isinstance(items, list):
        return jsonify({'error': 'No items provided'}), 400

    payment_method = data.get('payment_method')
    if not payment_method:
        return jsonify({'error': 'Payment method is required'}), 400

    discount_amount = data.get('discount_amount', 0)
    discount_reason = data.get('discount_reason', '')

    # Validate session exists and not deleted
    session = PrivateSession.query.filter_by(id=session_id, deleted=False).first()
    if not session:
        return jsonify({'error': 'Invalid or deleted session'}), 400

    # Validate student exists
    student = PrivateStudent.query.get(student_id)
    if not student:
        return jsonify({'error': 'Student not found'}), 404

    if discount_amount < 0:
        return jsonify({'error': 'Discount amount cannot be negative'}), 400

    try:
        # Process items and calculate totals
        subtotal = 0
        purchase_items = []

        for item in items:
            book = PrivateBook.query.get(item.get('book_id'))
            if not book:
                return jsonify({'error': 'Book not found'}), 404

            qty = item.get('quantity')
            if not qty or qty <= 0:
                return jsonify({'error': 'Invalid quantity'}), 400
            if book.quantity < qty:
                return jsonify({'error': f'Not enough stock for {book.title}'}), 400

            item_total = book.price * qty
            subtotal += item_total

            purchase_items.append({
                'book_id': book.id,
                'title': book.title,
                'quantity': qty,
                'price': float(book.price),
                'class_name': book.class_name,
                'total': float(item_total)
            })

            # Deduct stock
            book.quantity -= qty

        # Validate discount vs subtotal
        if discount_amount > subtotal:
            return jsonify({
                'error': f'Discount amount (₦{discount_amount:,.2f}) cannot exceed subtotal (₦{subtotal:,.2f})',
                'max_allowed_discount': subtotal
            }), 400

        total_amount = subtotal - discount_amount

        discount_percentage = (discount_amount / subtotal * 100) if subtotal > 0 else 0
        if discount_percentage > 0 and not discount_reason:
            return jsonify({
                'error': f'Discount reason required.'
            }), 400

        # Generate receipt number
        receipt_number = generate_receipt_number()

        # Create receipt
        receipt = PrivateBookReceipt(
            receipt_number=receipt_number,
            student_id=student.id,
            session_id=session.id,
            term=session.term,
            total_amount=total_amount,
            discount_amount=discount_amount,
            discount_reason=discount_reason,
            payment_method=payment_method,
            cashier_id=current_user.id,
            items=purchase_items
        )

        db.session.add(receipt)
        db.session.commit()

        return jsonify({
            'success': True,
            'receipt': receipt.to_dict()
        })

    except Exception as e:
        db.session.rollback()
        return jsonify({'error': f'Purchase failed: {str(e)}'}), 500

@app.route('/api/private/students/<student_id>/class-books', methods=['GET'])
@login_required
@require_password_changed
@requires_roles('private', 'Cashier')
def get_private_student_class_books(student_id):
    """Get all books for the student's class (for "Add All Books" feature)."""
    student = PrivateStudent.query.get(student_id)
    if not student:
        return jsonify({'error': 'Student not found'}), 404

    # Get all books for the student's class code
    books = PrivateBook.query.filter_by(class_code=student.class_code).all()
    
    return jsonify({
        'books': [{
            'id': book.id,
            'title': book.title,
            'author': book.author,
            'price': float(book.price),
            'quantity': book.quantity,
            'class_name': book.class_name,
            'class_code': book.class_code
        } for book in books]
    }), 200



# ==================== PRIVATE SCHOOL STUDENT MANAGEMENT ====================

@app.route('/api/private/students/<student_id>', methods=['PUT'])
@login_required
@require_password_changed
@requires_roles('private', 'Cashier')
def update_private_student(student_id):
    """Update a private student's name, phone, or email."""
    student = PrivateStudent.query.get(student_id)
    if not student:
        return jsonify({'error': 'Student not found'}), 404

    data = request.get_json()
    name = (data.get('name') or '').strip()
    phone = (data.get('phone') or '').strip()
    email = (data.get('email') or '').strip()

    if not name or not phone:
        return jsonify({'error': 'Name and phone are required'}), 400
    if email and not is_valid_email(email):
        return jsonify({'error': 'Invalid email address'}), 400

    has_changes = False
    if student.name != name:
        student.name = name
        has_changes = True
    if student.phone != phone:
        student.phone = phone
        has_changes = True
    if student.email != email:
        student.email = email if email else None
        has_changes = True

    if not has_changes:
        return jsonify({'error': 'No changes were made'}), 400

    db.session.commit()
    return jsonify({'success': True, 'student': student.to_dict()}), 200


@app.route('/api/private/students/<student_id>/pay-fees', methods=['POST'])
@login_required
@require_password_changed
@requires_roles('private', 'Cashier')
def pay_private_school_fees(student_id):
    """
    Change a private student's class.
    Creates a new PrivateEnrollmentReceipt for the fees charged.
    """
    student = PrivateStudent.query.get(student_id)
    if not student:
        return jsonify({'error': 'Student not found'}), 404

    data = request.get_json()

    session_id = data.get('session_id')
    class_code = data.get('class_code')
    selected_items = data.get('selected_items', [])   # list of {id, name, amount}
    payment_method = data.get('payment_method')
    discount_amount = data.get('discount_amount', 0)
    discount_reason = data.get('discount_reason', '')

    additional_amount = data.get('additional_amount', 0)
    additional_reason = data.get('additional_reason', '')
    
    # Validation
    if not session_id:
        return jsonify({'error': 'Session is required'}), 400
    if not class_code:
        return jsonify({'error': 'Class is required'}), 400
    if not selected_items:
        return jsonify({'error': 'At least one fee item must be selected'}), 400
    if discount_amount < 0 or additional_amount < 0:
        return jsonify({'error': 'Amounts cannot be negative'}), 400
    
    # Mutual exclusivity
    if discount_amount > 0 and additional_amount > 0:
        return jsonify({'error': 'Cannot apply both discount and additional fee. Choose one.'}), 400
    
    last_receipt = PrivateEnrollmentReceipt.query.filter_by(student_id=student.id)\
        .order_by(PrivateEnrollmentReceipt.created_at.desc()).first()

    if last_receipt and last_receipt.session_id == session_id:
        return jsonify({
            'error': f'Student already paid fees for session "{last_receipt.session.name}" (Term {last_receipt.term}). Cannot pay again for the same session and term'
        }), 400

    session = PrivateSession.query.filter_by(id=session_id, deleted=False).first()
    if not session:
        return jsonify({'error': 'Invalid or deleted session'}), 400

    class_obj = PrivateSchoolClass.query.filter_by(code=class_code).first()
    if not class_obj:
        return jsonify({'error': 'Invalid class'}), 400

    # Calculate subtotal
    subtotal = sum(item['amount'] for item in selected_items)
    
    if discount_amount > subtotal:
        return jsonify({
            'error': f'Discount amount (₦{discount_amount:,.2f}) cannot exceed subtotal (₦{subtotal:,.2f})',
            'max_allowed_discount': subtotal
        }), 400

    discount_percentage = (discount_amount / subtotal * 100) if subtotal > 0 else 0
    if discount_percentage > 0 and not discount_reason:
        return jsonify({
            'error': f'Discount reason required.'
        }), 400

    if additional_amount > 0 and not additional_reason:
        return jsonify({'error': 'Additional fee reason required.'}), 400
    
    total_amount = subtotal - discount_amount + additional_amount

    # Generate receipt number
    # receipt_number = generate_private_receipt_number(session.id, session.term)

    receipt_number = generate_receipt_number()
    pass_number = get_next_pass_number(session.id)

    # Create receipt (this acts as a "class change" receipt)
    receipt = PrivateEnrollmentReceipt(
        receipt_number=receipt_number,
        student_id=student.id,
        session_id=session.id,
        term=session.term,
        class_code=class_code,
        items=selected_items,
        discount_amount=discount_amount,
        discount_reason=discount_reason,
        total_amount=total_amount,
        payment_method=payment_method,
        cashier_id=current_user.id,
        pass_number=pass_number,
        additional_amount=additional_amount,
        additional_reason=additional_reason,
    )

    # Update student's class
    student.class_code = class_code

    db.session.add(receipt)
    db.session.commit()

    return jsonify({
        'success': True,
        'receipt': receipt.to_dict(),
        'student': student.to_dict()
    }), 200


@app.route('/api/private/analyst/cashiers', methods=['GET'])
@login_required
@require_password_changed
@requires_roles('private', 'Analyst')
def get_private_cashiers():
    cashier_role = PrivateRole.query.filter_by(name='Cashier').first()
    if not cashier_role:
        return jsonify([])
    cashiers = cashier_role.users.all()
    return jsonify([{'id': u.id, 'name': u.name} for u in cashiers])


@app.route('/api/private/analyst/transactions', methods=['GET'])
@login_required
@require_password_changed
@requires_roles('private', 'Analyst')
def get_private_transactions():
    start_date = request.args.get('start_date')
    end_date = request.args.get('end_date')
    payment_method = request.args.get('payment_method')
    transaction_type = request.args.get('transaction_type')   # 'form', 'school_fees', 'uniform', 'books'
    min_amount = request.args.get('min_amount', type=float)
    max_amount = request.args.get('max_amount', type=float)
    cashier_id = request.args.get('cashier_id')
    class_code = request.args.get('class_code')
    session_id = request.args.get('session_id')
    # fee_item = request.args.get('fee_item')

    all_items = []

    # ---------- Enrolment receipts – each item with proportional discount ----------
    for r in PrivateEnrollmentReceipt.query.all():
        if not _check_date(r.created_at, start_date, end_date):
            continue
        if payment_method and r.payment_method != payment_method:
            continue
        if cashier_id and r.cashier_id != cashier_id:
            continue
        if transaction_type and transaction_type not in ['form', 'school_fees']:
            continue
        if class_code and r.class_code != class_code:
            continue
        if session_id and r.session_id != session_id:
            continue

        items_list = r.items
        subtotal_items = sum(float(item.get('amount', 0)) for item in items_list)
        total_discount = float(r.discount_amount) if r.discount_amount else 0
        total_additional = float(r.additional_amount) if r.additional_amount else 0

        for item in items_list:
            amount = float(item.get('amount', 0))
            item_name = item.get('name', '')
            txn_type = 'form' if item_name == 'Form Fee' else 'school_fees'
            if transaction_type and transaction_type != txn_type:
                continue
            if (min_amount is not None and amount < min_amount) or (max_amount is not None and amount > max_amount):
                continue

            # Proportional discount
            item_discount = 0
            if total_discount > 0 and subtotal_items > 0:
                discount_ratio = amount / subtotal_items
                item_discount = total_discount * discount_ratio

            # Proportional additional fee
            item_additional = 0
            if total_additional > 0 and subtotal_items > 0:
                additional_ratio = amount / subtotal_items
                item_additional = total_additional * additional_ratio

            item_total = amount - item_discount + item_additional

            all_items.append({
                'id': f"{r.id}_{item['id']}",
                'receipt_number': r.receipt_number,
                'date': r.created_at,
                'type': txn_type,
                'student_name': r.student.name,
                'student_phone': r.student.phone,
                'student_email': r.student.email or '',
                'amount': item_total,
                'subtotal': amount,
                'discount_amount': item_discount,
                'discount_reason': r.discount_reason or '',
                'additional_amount': item_additional,      # <-- distributed amount for this item
                'additional_reason': r.additional_reason or '',
                'discount_percentage': (item_discount / amount * 100) if amount > 0 else 0,
                'payment_method': r.payment_method,
                'cashier_id': r.cashier_id,
                'cashier_name': r.cashier.name,
                'class_code': r.class_code,
                'session_name': r.session.name,
                'term': r.term,
                'item_title': item_name,
                'quantity': 1,
                'session_id': r.session_id
            })

    for r in PrivateFormReceipt.query.all():
        if not _check_date(r.created_at, start_date, end_date):
            continue
        if payment_method and r.payment_method != payment_method:
            continue
        if cashier_id and r.cashier_id != cashier_id:
            continue
        if transaction_type and transaction_type != 'form':
            continue
        if class_code and r.student.class_code != class_code:
            continue
        if session_id and r.session_id != session_id:
            continue

        amount = float(r.total_amount_float)
        if (min_amount is not None and amount < min_amount) or (max_amount is not None and amount > max_amount):
            continue

        all_items.append({
            'id': r.id,
            'receipt_number': r.receipt_number,
            'date': r.created_at,
            'type': 'form',
            'student_name': r.student.name,
            'student_phone': r.student.phone,
            'student_email': r.student.email or '',
            'amount': amount,
            'subtotal': float(r.form_price),
            'discount_amount': float(r.discount_amount),
            'discount_reason': r.discount_reason,
            'discount_percentage': (float(r.discount_amount) / float(r.form_price) * 100) if float(r.form_price) > 0 else 0,
            'payment_method': r.payment_method,
            'cashier_id': r.cashier_id,
            'cashier_name': r.cashier.name,
            'class_code': r.student.class_code,
            'session_name': r.session.name,
            'term': r.term,
            'item_title': f"Form ({r.form_class_group})",
            'quantity': 1,
            'session_id': r.session_id
        })

    # ---------- Book receipts – each book item with proportional discount ----------
    for r in PrivateUniformReceipt.query.all():
        if not _check_date(r.created_at, start_date, end_date):
            continue
        if payment_method and r.payment_method != payment_method:
            continue
        if cashier_id and r.cashier_id != cashier_id:
            continue
        if transaction_type and transaction_type != 'uniform':
            continue
        if class_code and r.student.class_code != class_code:
            continue
        if session_id and r.session_id != session_id:
            continue

        subtotal = float(r.total_amount or 0) + float(r.discount_amount or 0)
        total_discount = float(r.discount_amount or 0)

        for item in r.items:
            item_subtotal = float(item.get('total', 0))
            if (min_amount is not None and item_subtotal < min_amount) or (max_amount is not None and item_subtotal > max_amount):
                continue

            item_discount = 0
            if total_discount > 0 and subtotal > 0:
                discount_ratio = item_subtotal / subtotal
                item_discount = total_discount * discount_ratio
            item_total = item_subtotal - item_discount

            all_items.append({
                'id': f"{r.id}_{item.get('uniform_id')}",
                'receipt_number': r.receipt_number,
                'date': r.created_at,
                'type': 'uniform',
                'student_name': r.student.name,
                'student_phone': r.student.phone,
                'student_email': r.student.email or '',
                'amount': item_total,
                'subtotal': item_subtotal,
                'discount_amount': item_discount,
                'discount_reason': r.discount_reason,
                'discount_percentage': (item_discount / item_subtotal * 100) if item_subtotal > 0 else 0,
                'payment_method': r.payment_method,
                'cashier_id': r.cashier_id,
                'cashier_name': r.cashier.name,
                'class_code': r.student.class_code,
                'session_name': r.session.name,
                'term': r.term,
                'item_title': item.get('title'),
                'quantity': item.get('quantity'),
                'session_id': r.session_id
            })
    
    for r in PrivateBookReceipt.query.all():
        if not _check_date(r.created_at, start_date, end_date):
            continue
        if payment_method and r.payment_method != payment_method:
            continue
        if cashier_id and r.cashier_id != cashier_id:
            continue
        if transaction_type and transaction_type != 'books':
            continue
        if class_code and r.student.class_code != class_code:
            continue
        if session_id and r.session_id != session_id:
            continue

        subtotal = float(r.total_amount or 0) + float(r.discount_amount or 0)
        total_discount = float(r.discount_amount or 0)

        for item in r.items:
            item_subtotal = float(item.get('total', 0))
            if (min_amount is not None and item_subtotal < min_amount) or (max_amount is not None and item_subtotal > max_amount):
                continue

            item_discount = 0
            if total_discount > 0 and subtotal > 0:
                discount_ratio = item_subtotal / subtotal
                item_discount = total_discount * discount_ratio
            item_total = item_subtotal - item_discount

            all_items.append({
                'id': f"{r.id}_{item.get('book_id')}",
                'receipt_number': r.receipt_number,
                'date': r.created_at,
                'type': 'books',
                'student_name': r.student.name,
                'student_phone': r.student.phone,
                'student_email': r.student.email or '',
                'amount': item_total,
                'subtotal': item_subtotal,
                'discount_amount': item_discount,
                'discount_reason': r.discount_reason,
                'discount_percentage': (item_discount / item_subtotal * 100) if item_subtotal > 0 else 0,
                'payment_method': r.payment_method,
                'cashier_id': r.cashier_id,
                'cashier_name': r.cashier.name,
                'class_code': r.student.class_code,
                'session_name': r.session.name,
                'term': r.term,
                'item_title': item.get('title'),
                'quantity': item.get('quantity'),
                'session_id': r.session_id
            })
    # ---------- Islamiyya receipts ----------
    for r in IslamiyyaPaymentReceipt.query.all():
        if not _check_date(r.created_at, start_date, end_date):
            continue
        if payment_method and r.payment_method != payment_method:
            continue
        if cashier_id and r.cashier_id != cashier_id:
            continue
        if transaction_type and transaction_type != 'islamiyya':
            continue
        if class_code and r.student.class_code != class_code:
            continue
        if session_id and r.session_id != session_id:
            continue

        amount = r.total_amount_float
        if (min_amount is not None and amount < min_amount) or (max_amount is not None and amount > max_amount):
            continue

        all_items.append({
            'id': r.id,
            'receipt_number': r.receipt_number,
            'date': r.created_at,
            'type': 'islamiyya',
            'student_name': r.student.name,
            'student_phone': r.student.phone,
            'student_email': r.student.email or '',
            'amount': amount,
            'subtotal': r.form_fee_charged_float + r.term_fee_charged_float,
            'discount_amount': r.discount_amount_float,
            'discount_reason': r.discount_reason,
            'discount_percentage': (r.discount_amount_float / (r.form_fee_charged_float + r.term_fee_charged_float) * 100) if (r.form_fee_charged_float + r.term_fee_charged_float) > 0 else 0,
            'payment_method': r.payment_method,
            'cashier_id': r.cashier_id,
            'cashier_name': r.cashier.name,
            'class_code': r.student.class_code,
            'session_name': r.session.name,
            'term': r.term,
            'item_title': 'Islamiyya Fees',
            'quantity': 1,
            'session_id': r.session_id
        })
        
    all_items.sort(key=lambda x: x['date'], reverse=True)
    total_amount = sum(i['amount'] for i in all_items)
    total_discount = sum(i['discount_amount'] for i in all_items)

    return jsonify({
        'transactions': [{**t, 'date': t['date'].isoformat()} for t in all_items],
        'total_count': len(all_items),
        'total_amount': total_amount,
        'total_discount': total_discount
    })

@app.route('/api/private/analyst/students', methods=['GET'])
@login_required
@require_password_changed
@requires_roles('private', 'Analyst')
def get_private_analyst_students():
    class_filter = request.args.get('class_code')
    students = PrivateStudent.query.all()
    result = []

    for s in students:
        if class_filter and s.class_code != class_filter:
            continue

        total_spent = 0.0
        receipts_count = 0

        # 1. Enrollment receipts – use the final total_amount (already includes discount & additional fee)
        for r in s.receipts:  # PrivateEnrollmentReceipts
            receipts_count += 1
            total_spent += r.total_amount_float

        # 2. Book receipts
        for r in PrivateBookReceipt.query.filter_by(student_id=s.id):
            receipts_count += 1
            if r.items:
                subtotal = float(r.total_amount or 0) + float(r.discount_amount or 0)
                total_discount = float(r.discount_amount or 0)
                for item in r.items:
                    item_subtotal = float(item.get('total', 0))
                    item_discount = 0
                    if total_discount > 0 and subtotal > 0:
                        discount_ratio = item_subtotal / subtotal
                        item_discount = total_discount * discount_ratio
                    total_spent += (item_subtotal - item_discount)
            else:
                total_spent += float(r.total_amount or 0)

        # 3. Uniform receipts
        for r in PrivateUniformReceipt.query.filter_by(student_id=s.id):
            receipts_count += 1
            subtotal = float(r.total_amount or 0) + float(r.discount_amount or 0)
            total_discount = float(r.discount_amount or 0)
            for item in r.items:
                item_subtotal = float(item.get('total', 0))
                item_discount = 0
                if total_discount > 0 and subtotal > 0:
                    discount_ratio = item_subtotal / subtotal
                    item_discount = total_discount * discount_ratio
                total_spent += (item_subtotal - item_discount)

        # 4. Form receipts (standalone)
        for r in PrivateFormReceipt.query.filter_by(student_id=s.id):
            receipts_count += 1
            total_spent += r.total_amount_float

        # 5. Islamiyya receipts (if applicable)
        for r in IslamiyyaPaymentReceipt.query.filter_by(student_id=s.id):
            receipts_count += 1
            total_spent += r.total_amount_float

        result.append({
            'id': s.id,
            'name': s.name,
            'phone': s.phone,
            'email': s.email or '',
            'class_code': s.class_code,
            'class_name': s.class_obj.name if s.class_obj else '',
            'total_spent': total_spent,
            'receipts_count': receipts_count,
            'created_at': s.created_at.isoformat()
        })

    result.sort(key=lambda x: x['name'])
    return jsonify(result)

@app.route('/api/private/analyst/summary', methods=['GET'])
@login_required
@require_password_changed
@requires_roles('private', 'Analyst')
def get_private_analyst_summary():
    start_date = request.args.get('start_date')
    end_date = request.args.get('end_date')

    all_items = []

    # Enrolment – split Form Fee vs School Fees
    for r in PrivateEnrollmentReceipt.query.all():
        if not _check_date(r.created_at, start_date, end_date):
            continue
        subtotal_items = sum(float(item.get('amount', 0)) for item in r.items)
        total_discount = float(r.discount_amount) if r.discount_amount else 0
        total_additional = float(r.additional_amount) if r.additional_amount else 0

        for item in r.items:
            amount = float(item.get('amount', 0))
            item_name = item.get('name', '')
            txn_type = 'form' if item_name == 'Form Fee' else 'school_fees'
            
            item_discount = 0
            if total_discount > 0 and subtotal_items > 0:
                discount_ratio = amount / subtotal_items
                item_discount = total_discount * discount_ratio
            
            # Proportional additional fee
            item_additional = 0
            if total_additional > 0 and subtotal_items > 0:
                additional_ratio = amount / subtotal_items
                item_additional = total_additional * additional_ratio
                
            item_total = amount - item_discount + item_additional
            if item_total > 0:
                all_items.append({
                    'date': r.created_at,
                    'amount': item_total,
                    'type': txn_type,
                    'payment_method': r.payment_method
                })

    # Standalone Form Receipts
    for r in PrivateFormReceipt.query.all():
        if not _check_date(r.created_at, start_date, end_date):
            continue
        amount = float(r.total_amount_float)
        if amount > 0:
            all_items.append({
                'date': r.created_at,
                'amount': amount,
                'type': 'form',
                'payment_method': r.payment_method
            })

    # Uniform Receipts
    for r in PrivateUniformReceipt.query.all():
        if not _check_date(r.created_at, start_date, end_date):
            continue
        total_discount = float(r.discount_amount or 0)
        subtotal = float(r.total_amount or 0) + total_discount
        for item in r.items:
            item_subtotal = float(item.get('total', 0))
            item_discount = 0
            if total_discount > 0 and subtotal > 0:
                discount_ratio = item_subtotal / subtotal
                item_discount = total_discount * discount_ratio
            item_total = item_subtotal - item_discount
            if item_total > 0:
                all_items.append({
                    'date': r.created_at,
                    'amount': item_total,
                    'type': 'uniform',
                    'payment_method': r.payment_method
                })

    # Book Receipts (type 'books')
    for r in PrivateBookReceipt.query.all():
        if not _check_date(r.created_at, start_date, end_date):
            continue
        total_discount = float(r.discount_amount or 0)
        subtotal = float(r.total_amount or 0) + total_discount
        for item in r.items:
            item_subtotal = float(item.get('total', 0))
            item_discount = 0
            if total_discount > 0 and subtotal > 0:
                discount_ratio = item_subtotal / subtotal
                item_discount = total_discount * discount_ratio
            item_total = item_subtotal - item_discount
            if item_total > 0:
                all_items.append({
                    'date': r.created_at,
                    'amount': item_total,
                    'type': 'books',
                    'payment_method': r.payment_method
                })
    # ---------- Islamiyya receipts ----------
    for r in IslamiyyaPaymentReceipt.query.all():
        if not _check_date(r.created_at, start_date, end_date):
            continue
        amount = r.total_amount_float
        if amount > 0:
            all_items.append({
                'date': r.created_at,
                'amount': amount,
                'type': 'islamiyya',
                'payment_method': r.payment_method
            })

    total_revenue = sum(i['amount'] for i in all_items)
    total_transactions = len(all_items)

    by_type = {'form': 0, 'school_fees': 0, 'uniform': 0, 'books': 0, 'islamiyya':0}
    by_payment = {}
    for i in all_items:
        by_type[i['type']] += i['amount']
        by_payment[i['payment_method']] = by_payment.get(i['payment_method'], 0) + i['amount']

    total_students = PrivateStudent.query.count()
    enrolled_students = PrivateStudent.query.filter(PrivateStudent.class_code.isnot(None)).count()

    return jsonify({
        'total_revenue': round(total_revenue, 2),
        'total_transactions': total_transactions,
        'form_revenue': round(by_type['form'], 2),
        'school_fees_revenue': round(by_type['school_fees'], 2),
        'uniform_revenue': round(by_type['uniform'], 2),
        'books_revenue': round(by_type['books'], 2),
        'islamiyya_revenue': round(by_type['islamiyya'], 2),
        'by_type': {k: round(v, 2) for k, v in by_type.items()},
        'by_payment': {k: round(v, 2) for k, v in by_payment.items()},
        'total_students': total_students,
        'enrolled_students': enrolled_students
    })

def _check_date(dt, start_str, end_str):
    if start_str:
        start = datetime.fromisoformat(start_str) if 'T' in start_str else datetime.fromisoformat(start_str + 'T00:00:00')
        if dt < start:
            return False
    if end_str:
        end = datetime.fromisoformat(end_str) if 'T' in end_str else datetime.fromisoformat(end_str + 'T23:59:59')
        if dt > end:
            return False
    return True
    

@app.route('/api/private/analyst/export', methods=['POST'])
@login_required
@require_password_changed
@requires_roles('private', 'Analyst')
def export_private_full():
    """Export all data (no filters) for private school."""
    data = request.get_json() or {}
    export_type = data.get('type')  # 'transactions' or 'students'
    return _build_private_export(export_type, {})


@app.route('/api/private/analyst/export-filtered', methods=['POST'])
@login_required
@require_password_changed
@requires_roles('private', 'Analyst')
def export_private_filtered():
    """Export filtered data for private school."""
    data = request.get_json() or {}
    export_type = data.get('type')
    filters = data.get('filters', {})
    return _build_private_export(export_type, filters)

def _build_private_export(export_type, filters):
    from openpyxl import Workbook
    from openpyxl.styles import Font, Alignment
    from io import BytesIO
    import openpyxl

    wb = Workbook()
    wb.remove(wb.active)

    header_font = Font(bold=True, size=11)
    title_font = Font(bold=True, size=14)
    label_font = Font(bold=True, size=11)
    value_font = Font(size=11)

    def format_phone(phone):
        return str(phone) if phone else 'N/A'

    def safe_date(date_str, end=False):
        if not date_str:
            return None
        try:
            return datetime.fromisoformat(date_str + ('T23:59:59' if end else ''))
        except:
            return None

    def auto_width(ws, max_width=40):
        for col in ws.columns:
            max_length = 0
            col_letter = None
            for cell in col:
                if isinstance(cell, openpyxl.cell.cell.MergedCell):
                    continue
                if col_letter is None:
                    col_letter = cell.column_letter
                try:
                    if cell.value:
                        max_length = max(max_length, len(str(cell.value)))
                except:
                    pass
            if col_letter:
                ws.column_dimensions[col_letter].width = min(max_length + 2, max_width)

    def format_currency_cells(ws, currency_columns, start_row=2):
        for row in ws.iter_rows(min_row=start_row):
            for cell in row:
                if cell.column in currency_columns and isinstance(cell.value, (int, float)):
                    cell.number_format = '#,##0'

    # Summary sheet (unchanged)
    summary_ws = wb.create_sheet("Summary")
    has_filters = any(filters.values())
    title_cell = summary_ws['A1']
    title_cell.value = f"{'FILTERED' if has_filters else 'ALL'} {export_type.upper()} EXPORT - PRIVATE SCHOOL"
    title_cell.font = title_font
    summary_ws.merge_cells('A1:D1')

    summary_ws['A3'] = "Export Date:"
    summary_ws['B3'] = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    summary_ws['A3'].font = label_font
    summary_ws['B3'].font = value_font

    row = 5
    summary_ws[f'A{row}'] = "SUMMARY STATISTICS"
    summary_ws[f'A{row}'].font = Font(bold=True, size=12)
    row += 2

    if export_type == 'transactions':
        revenue_row = row
        summary_ws[f'A{revenue_row}'] = "Total Revenue:"
        summary_ws[f'B{revenue_row}'] = "Pending calculation"
        summary_ws[f'A{revenue_row}'].font = label_font
        row += 1
        transactions_row = row
        summary_ws[f'A{transactions_row}'] = "Total Transactions:"
        summary_ws[f'B{transactions_row}'] = "Pending calculation"
        summary_ws[f'A{transactions_row}'].font = label_font
        row += 1
    else:
        students_row = row
        summary_ws[f'A{students_row}'] = "Total Students:"
        summary_ws[f'B{students_row}'] = "Pending calculation"
        summary_ws[f'A{students_row}'].font = label_font
        row += 1
        spent_row = row
        summary_ws[f'A{spent_row}'] = "Total Spent:"
        summary_ws[f'B{spent_row}'] = "Pending calculation"
        summary_ws[f'A{spent_row}'].font = label_font
        row += 1

    row += 1
    if has_filters:
        summary_ws[f'A{row}'] = "FILTERS APPLIED"
        summary_ws[f'A{row}'].font = Font(bold=True, size=12)
        row += 2
        if export_type == 'transactions':
            if filters.get('start_date'):
                summary_ws[f'A{row}'] = "Start Date:"; summary_ws[f'B{row}'] = filters['start_date']; row += 1
            if filters.get('end_date'):
                summary_ws[f'A{row}'] = "End Date:"; summary_ws[f'B{row}'] = filters['end_date']; row += 1
            if filters.get('payment_method'):
                summary_ws[f'A{row}'] = "Payment Method:"; summary_ws[f'B{row}'] = filters['payment_method']; row += 1
            if filters.get('transaction_type'):
                summary_ws[f'A{row}'] = "Transaction Type:"; summary_ws[f'B{row}'] = filters['transaction_type']; row += 1
            if filters.get('class_code'):
                class_obj = PrivateSchoolClass.query.filter_by(code=filters['class_code']).first()
                summary_ws[f'A{row}'] = "Class:"; summary_ws[f'B{row}'] = class_obj.name if class_obj else filters['class_code']; row += 1
            if filters.get('cashier_id'):
                cashier = User.query.get(filters['cashier_id'])
                summary_ws[f'A{row}'] = "Cashier:"; summary_ws[f'B{row}'] = cashier.name if cashier else filters['cashier_id']; row += 1
            if filters.get('session_id'):
                session = PrivateSession.query.get(filters['session_id'])
                summary_ws[f'A{row}'] = "Session:"; summary_ws[f'B{row}'] = f"{session.name} (Term {session.term})" if session else filters['session_id']; row += 1
            if filters.get('min_amount'):
                summary_ws[f'A{row}'] = "Min Amount:"; summary_ws[f'B{row}'] = f"₦{float(filters['min_amount']):,.0f}"; row += 1
            if filters.get('max_amount'):
                summary_ws[f'A{row}'] = "Max Amount:"; summary_ws[f'B{row}'] = f"₦{float(filters['max_amount']):,.0f}"; row += 1
        else:
            if filters.get('class_code'):
                class_obj = PrivateSchoolClass.query.filter_by(code=filters['class_code']).first()
                summary_ws[f'A{row}'] = "Class:"; summary_ws[f'B{row}'] = class_obj.name if class_obj else filters['class_code']; row += 1

    summary_ws.column_dimensions['A'].width = 20
    summary_ws.column_dimensions['B'].width = 30

    # Data sheet
    data_ws = wb.create_sheet(f"{export_type.capitalize()} Data")

    if export_type == 'transactions':
        start_dt = safe_date(filters.get('start_date'))
        end_dt = safe_date(filters.get('end_date'), end=True)
        payment_method = filters.get('payment_method')
        transaction_type_filter = filters.get('transaction_type')
        min_amount = filters.get('min_amount')
        max_amount = filters.get('max_amount')
        cashier_id = filters.get('cashier_id')
        class_code = filters.get('class_code')
        session_id = filters.get('session_id')
        search_query_raw = filters.get('search_query')
        search_query = search_query_raw.lower() if search_query_raw else ''

        def match_date(dt):
            return (not start_dt or dt >= start_dt) and (not end_dt or dt <= end_dt)
        def match_search(text):
            if not search_query:
                return True
            return search_query in str(text).lower()

        rows = []
        total_revenue = 0.0

        # Enrolment split with additional fee
        for r in PrivateEnrollmentReceipt.query.all():
            if not match_date(r.created_at):
                continue
            if payment_method and r.payment_method != payment_method:
                continue
            if cashier_id and r.cashier_id != cashier_id:
                continue
            if class_code and r.class_code != class_code:
                continue
            if session_id and r.session_id != session_id:
                continue
            if not (match_search(r.receipt_number) or match_search(r.student.name)):
                continue

            subtotal_items = sum(float(item.get('amount', 0)) for item in r.items)
            total_discount = float(r.discount_amount) if r.discount_amount else 0
            total_additional = float(r.additional_amount) if r.additional_amount else 0

            for item in r.items:
                amount = float(item.get('amount', 0))
                item_name = item.get('name', '')
                txn_type = 'form' if item_name == 'Form Fee' else 'school_fees'
                if transaction_type_filter and transaction_type_filter != txn_type:
                    continue
                if (min_amount is not None and amount < min_amount) or (max_amount is not None and amount > max_amount):
                    continue

                # Proportional discount
                item_discount = 0
                if total_discount > 0 and subtotal_items > 0:
                    discount_ratio = amount / subtotal_items
                    item_discount = total_discount * discount_ratio

                # Proportional additional fee
                item_additional = 0
                if total_additional > 0 and subtotal_items > 0:
                    additional_ratio = amount / subtotal_items
                    item_additional = total_additional * additional_ratio

                item_total = amount - item_discount + item_additional

                rows.append([
                    r.receipt_number,
                    r.created_at.strftime('%Y-%m-%d %H:%M'),
                    txn_type,
                    r.student.name,
                    format_phone(r.student.phone),
                    r.student.email or '-',
                    item_name,
                    1,
                    amount,
                    item_discount,
                    item_additional,                    # ADDED: proportional additional amount
                    item_total,
                    r.discount_reason or '-',
                    r.additional_reason or '-',         # ADDED: additional fee reason
                    r.payment_method,
                    r.cashier.name,
                    r.session.name,
                    r.term
                ])
                total_revenue += item_total

        # Standalone Form (no additional fee, so columns zero/empty)
        for r in PrivateFormReceipt.query.all():
            if not match_date(r.created_at):
                continue
            if payment_method and r.payment_method != payment_method:
                continue
            if cashier_id and r.cashier_id != cashier_id:
                continue
            if class_code and r.student.class_code != class_code:
                continue
            if session_id and r.session_id != session_id:
                continue
            if not (match_search(r.receipt_number) or match_search(r.student.name)):
                continue
            amount = float(r.total_amount_float)
            if (min_amount is not None and amount < min_amount) or (max_amount is not None and amount > max_amount):
                continue
            rows.append([
                r.receipt_number,
                r.created_at.strftime('%Y-%m-%d %H:%M'),
                'form',
                r.student.name,
                format_phone(r.student.phone),
                r.student.email or '-',
                f"Form ({r.form_class_group})",
                1,
                float(r.form_price),
                float(r.discount_amount),
                0,                                      # no additional fee
                amount,
                r.discount_reason or '-',
                '-',                                     # no additional reason
                r.payment_method,
                r.cashier.name,
                r.session.name,
                r.term
            ])
            total_revenue += amount

        # Uniform (no additional fee)
        for r in PrivateUniformReceipt.query.all():
            if not match_date(r.created_at):
                continue
            if payment_method and r.payment_method != payment_method:
                continue
            if cashier_id and r.cashier_id != cashier_id:
                continue
            if class_code and r.student.class_code != class_code:
                continue
            if session_id and r.session_id != session_id:
                continue
            if not (match_search(r.receipt_number) or match_search(r.student.name)):
                continue
            subtotal = float(r.total_amount or 0) + float(r.discount_amount or 0)
            total_discount = float(r.discount_amount or 0)
            for item in r.items:
                item_subtotal = float(item.get('total', 0))
                if (min_amount is not None and item_subtotal < min_amount) or (max_amount is not None and item_subtotal > max_amount):
                    continue
                item_discount = 0
                if total_discount > 0 and subtotal > 0:
                    discount_ratio = item_subtotal / subtotal
                    item_discount = total_discount * discount_ratio
                item_total = item_subtotal - item_discount
                rows.append([
                    r.receipt_number,
                    r.created_at.strftime('%Y-%m-%d %H:%M'),
                    'uniform',
                    r.student.name,
                    format_phone(r.student.phone),
                    r.student.email or '-',
                    item.get('title'),
                    item.get('quantity', 1),
                    item_subtotal,
                    item_discount,
                    0,
                    item_total,
                    r.discount_reason or '-',
                    '-',
                    r.payment_method,
                    r.cashier.name,
                    r.session.name,
                    r.term
                ])
                total_revenue += item_total

        # Books (no additional fee)
        for r in PrivateBookReceipt.query.all():
            if not match_date(r.created_at):
                continue
            if payment_method and r.payment_method != payment_method:
                continue
            if cashier_id and r.cashier_id != cashier_id:
                continue
            if class_code and r.student.class_code != class_code:
                continue
            if session_id and r.session_id != session_id:
                continue
            if not (match_search(r.receipt_number) or match_search(r.student.name)):
                continue
            subtotal = float(r.total_amount or 0) + float(r.discount_amount or 0)
            total_discount = float(r.discount_amount or 0)
            for item in r.items:
                item_subtotal = float(item.get('total', 0))
                if (min_amount is not None and item_subtotal < min_amount) or (max_amount is not None and item_subtotal > max_amount):
                    continue
                item_discount = 0
                if total_discount > 0 and subtotal > 0:
                    discount_ratio = item_subtotal / subtotal
                    item_discount = total_discount * discount_ratio
                item_total = item_subtotal - item_discount
                rows.append([
                    r.receipt_number,
                    r.created_at.strftime('%Y-%m-%d %H:%M'),
                    'books',
                    r.student.name,
                    format_phone(r.student.phone),
                    r.student.email or '-',
                    item.get('title'),
                    item.get('quantity', 1),
                    item_subtotal,
                    item_discount,
                    0,
                    item_total,
                    r.discount_reason or '-',
                    '-',
                    r.payment_method,
                    r.cashier.name,
                    r.session.name,
                    r.term
                ])
                total_revenue += item_total

        # Islamiyya receipts (no additional fee)
        for r in IslamiyyaPaymentReceipt.query.all():
            if not match_date(r.created_at):
                continue
            if payment_method and r.payment_method != payment_method:
                continue
            if cashier_id and r.cashier_id != cashier_id:
                continue
            if class_code and r.student.class_code != class_code:
                continue
            if session_id and r.session_id != session_id:
                continue
            if not (match_search(r.receipt_number) or match_search(r.student.name)):
                continue

            amount = r.total_amount_float
            if (min_amount is not None and amount < min_amount) or (max_amount is not None and amount > max_amount):
                continue

            subtotal = r.form_fee_charged_float + r.term_fee_charged_float
            rows.append([
                r.receipt_number,
                r.created_at.strftime('%Y-%m-%d %H:%M'),
                'islamiyya',
                r.student.name,
                format_phone(r.student.phone),
                r.student.email or '-',
                'Islamiyya Fees',
                1,
                subtotal,
                r.discount_amount_float,
                0,
                amount,
                r.discount_reason or '-',
                '-',
                r.payment_method,
                r.cashier.name,
                r.session.name,
                r.term
            ])
            total_revenue += amount

        rows.sort(key=lambda x: x[1], reverse=True)
        summary_ws[f'B{revenue_row}'] = f"₦{total_revenue:,.0f}"
        summary_ws[f'B{transactions_row}'] = len(rows)

        # Updated headers: added "Additional Amt" and "Additional Reason"
        headers = ['Receipt Number', 'Date', 'Transaction Type', 'Student Name', 'Phone', 'Email',
                   'Item Name', 'Quantity', 'Subtotal', 'Discount Amt', 'Additional Amt', 'Total',
                   'Discount Reason', 'Additional Amt Reason', 'Payment Method', 'Cashier', 'Session', 'Term']
        data_ws.append(headers)
        for cell in data_ws[1]:
            cell.font = header_font
            cell.alignment = Alignment(horizontal="center", vertical="center")
        for row_data in rows:
            data_ws.append(row_data)
        # currency columns: Subtotal (col9), Discount Amt (col10), Additional Amt (col11), Total (col12)
        format_currency_cells(data_ws, currency_columns=[9, 10, 11, 12])
        auto_width(data_ws, 50)

    else:  # students export (unchanged)
        class_filter = filters.get('class_code')
        search_query_raw = filters.get('search_query')
        search_query = search_query_raw.lower() if search_query_raw else ''

        rows = []
        students = PrivateStudent.query.all()
        total_students_count = 0
        total_spent_all = 0.0

        for s in students:
            if class_filter and s.class_code != class_filter:
                continue
            if search_query:
                if not (search_query in s.name.lower() or search_query in s.phone.lower() or (s.email and search_query in s.email.lower())):
                    continue
            total_spent = 0.0
            receipt_count = 0
            # Enrolment receipts
            for r in s.receipts:
                receipt_count += 1
                # Use total_amount_float which already includes discount and additional
                total_spent += r.total_amount_float
            # Book receipts
            for r in PrivateBookReceipt.query.filter_by(student_id=s.id):
                receipt_count += 1
                if r.items:
                    subtotal = float(r.total_amount or 0) + float(r.discount_amount or 0)
                    total_discount = float(r.discount_amount or 0)
                    for item in r.items:
                        item_subtotal = float(item.get('total', 0))
                        if total_discount > 0 and subtotal > 0:
                            discount_ratio = item_subtotal / subtotal
                            item_discount = total_discount * discount_ratio
                        else:
                            item_discount = 0
                        total_spent += (item_subtotal - item_discount)
                else:
                    total_spent += float(r.total_amount or 0)
            # Uniform receipts
            for r in PrivateUniformReceipt.query.filter_by(student_id=s.id):
                receipt_count += 1
                subtotal = float(r.total_amount or 0) + float(r.discount_amount or 0)
                total_discount = float(r.discount_amount or 0)
                for item in r.items:
                    item_subtotal = float(item.get('total', 0))
                    if total_discount > 0 and subtotal > 0:
                        discount_ratio = item_subtotal / subtotal
                        item_discount = total_discount * discount_ratio
                    else:
                        item_discount = 0
                    total_spent += (item_subtotal - item_discount)
            # Form receipts (standalone)
            for r in PrivateFormReceipt.query.filter_by(student_id=s.id):
                receipt_count += 1
                total_spent += r.total_amount_float
            # Islamiyya receipts
            for r in IslamiyyaPaymentReceipt.query.filter_by(student_id=s.id):
                receipt_count += 1
                total_spent += r.total_amount_float

            rows.append({
                'name': s.name,
                'phone': format_phone(s.phone),
                'email': s.email or '-',
                'class_code': s.class_code,
                'class_name': s.class_obj.name if s.class_obj else '',
                'created_at': s.created_at.strftime('%Y-%m-%d') if s.created_at else 'N/A',
                'total_spent': total_spent,
                'receipts_count': receipt_count
            })
            total_students_count += 1
            total_spent_all += total_spent

        summary_ws[f'B{students_row}'] = total_students_count
        summary_ws[f'B{spent_row}'] = f"₦{total_spent_all:,.0f}"

        headers = ['Name', 'Phone', 'Email', 'Class Code', 'Class Name', 'Registration Date', 'Total Spent', 'Receipts Count']
        data_ws.append(headers)
        for cell in data_ws[1]:
            cell.font = header_font
            cell.alignment = Alignment(horizontal="center", vertical="center")
        for row_data in rows:
            data_ws.append([
                row_data['name'], row_data['phone'], row_data['email'],
                row_data['class_code'], row_data['class_name'], row_data['created_at'],
                row_data['total_spent'], row_data['receipts_count']
            ])
        format_currency_cells(data_ws, currency_columns=[7])
        auto_width(data_ws, 40)

    if 'Sheet' in wb.sheetnames:
        del wb['Sheet']

    output = BytesIO()
    wb.save(output)
    output.seek(0)
    filename = f"private_{export_type}_{'filtered' if has_filters else 'full'}_export.xlsx"
    return Response(
        output.getvalue(),
        mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        headers={'Content-Disposition': f'attachment; filename={filename}'}
    )


# ==================== PRIVATE SCHOOL CASHIER MY ANALYSIS ====================

@app.route('/api/private/cashier/transactions', methods=['GET'])
@login_required
@require_password_changed
@requires_roles('private', 'Cashier')
def get_private_cashier_transactions():
    """Get transactions for the logged-in cashier with filtering."""
    start_date = request.args.get('start_date')
    end_date = request.args.get('end_date')
    payment_method = request.args.get('payment_method')
    transaction_type = request.args.get('transaction_type')    # 'form', 'school_fees', 'uniform', 'books'
    class_code = request.args.get('class_code')
    # fee_item = request.args.get('fee_item')
    min_amount = request.args.get('min_amount', type=float)
    max_amount = request.args.get('max_amount', type=float)
    session_id = request.args.get('session_id')

    all_transactions = []

    # ---------- Enrolment receipts – split Form Fee vs School Fees ----------
    for r in PrivateEnrollmentReceipt.query.filter_by(cashier_id=current_user.id).all():
        if not _check_date(r.created_at, start_date, end_date):
            continue
        if payment_method and r.payment_method != payment_method:
            continue
        if transaction_type and transaction_type not in ['form', 'school_fees']:
            continue
        if class_code and r.class_code != class_code:
            continue
        if session_id and r.session_id != session_id:
            continue

        subtotal_items = sum(float(item.get('amount', 0)) for item in r.items)
        total_discount = float(r.discount_amount) if r.discount_amount else 0
        total_additional = float(r.additional_amount) if r.additional_amount else 0

        for item in r.items:
            amount = float(item.get('amount', 0))
            item_name = item.get('name', '')
            txn_type = 'form' if item_name == 'Form Fee' else 'school_fees'
            if transaction_type and transaction_type != txn_type:
                continue
            if (min_amount is not None and amount < min_amount) or (max_amount is not None and amount > max_amount):
                continue

            item_discount = 0
            if total_discount > 0 and subtotal_items > 0:
                discount_ratio = amount / subtotal_items
                item_discount = total_discount * discount_ratio

            # Proportional additional fee
            item_additional = 0
            if total_additional > 0 and subtotal_items > 0:
                additional_ratio = amount / subtotal_items
                item_additional = total_additional * additional_ratio

            item_total = amount - item_discount + item_additional
            
            all_transactions.append({
                'id': f"{r.id}_{item['id']}",
                'receipt_number': r.receipt_number,
                'date': r.created_at,
                'type': txn_type,
                'student_name': r.student.name,
                'student_phone': r.student.phone,
                'student_email': r.student.email or '',
                'amount': item_total,
                'subtotal': amount,
                'discount_amount': item_discount,
                'discount_reason': r.discount_reason if r.discount_amount else None,
                'discount_percentage': (item_discount / amount * 100) if amount > 0 else 0,
                'payment_method': r.payment_method,
                'cashier_id': r.cashier_id,
                'cashier_name': r.cashier.name,
                'class_code': r.class_code,
                'session_name': r.session.name,
                'term': r.term,
                'item_title': item_name,
                'quantity': 1,
                'session_id': r.session_id,
                'additional_amount': item_additional,
                'additional_reason': r.additional_reason or '',
            })

     # ---------- Standalone Form Receipts ----------
    for r in PrivateFormReceipt.query.filter_by(cashier_id=current_user.id).all():
        if not _check_date(r.created_at, start_date, end_date):
            continue
        if payment_method and r.payment_method != payment_method:
            continue
        if transaction_type and transaction_type != 'form':
            continue
        if class_code and r.student.class_code != class_code:
            continue
        if session_id and r.session_id != session_id:
            continue

        amount = float(r.total_amount_float)
        if (min_amount is not None and amount < min_amount) or (max_amount is not None and amount > max_amount):
            continue

        all_transactions.append({
            'id': r.id,
            'receipt_number': r.receipt_number,
            'date': r.created_at,
            'type': 'form',
            'student_name': r.student.name,
            'student_phone': r.student.phone,
            'student_email': r.student.email or '',
            'amount': amount,
            'subtotal': float(r.form_price),
            'discount_amount': float(r.discount_amount),
            'discount_reason': r.discount_reason,
            'discount_percentage': (float(r.discount_amount) / float(r.form_price) * 100) if float(r.form_price) > 0 else 0,
            'payment_method': r.payment_method,
            'cashier_id': r.cashier_id,
            'cashier_name': r.cashier.name,
            'class_code': r.student.class_code,
            'session_name': r.session.name,
            'term': r.term,
            'item_title': f"Form ({r.form_class_group})",
            'quantity': 1,
            'session_id': r.session_id
        })
    
    # ---------- Uniform Receipts ----------
    for r in PrivateUniformReceipt.query.filter_by(cashier_id=current_user.id).all():
        if not _check_date(r.created_at, start_date, end_date):
            continue
        if payment_method and r.payment_method != payment_method:
            continue
        if transaction_type and transaction_type != 'uniform':
            continue
        if class_code and r.student.class_code != class_code:
            continue
        if session_id and r.session_id != session_id:
            continue

        subtotal = float(r.total_amount or 0) + float(r.discount_amount or 0)
        total_discount = float(r.discount_amount or 0)

        for item in r.items:
            item_subtotal = float(item.get('total', 0))
            if (min_amount is not None and item_subtotal < min_amount) or (max_amount is not None and item_subtotal > max_amount):
                continue

            item_discount = 0
            if total_discount > 0 and subtotal > 0:
                discount_ratio = item_subtotal / subtotal
                item_discount = total_discount * discount_ratio
            item_total = item_subtotal - item_discount

            all_transactions.append({
                'id': f"{r.id}_{item.get('uniform_id')}",
                'receipt_number': r.receipt_number,
                'date': r.created_at,
                'type': 'uniform',
                'student_name': r.student.name,
                'student_phone': r.student.phone,
                'student_email': r.student.email or '',
                'amount': item_total,
                'subtotal': item_subtotal,
                'discount_amount': item_discount,
                'discount_reason': r.discount_reason,
                'discount_percentage': (item_discount / item_subtotal * 100) if item_subtotal > 0 else 0,
                'payment_method': r.payment_method,
                'cashier_id': r.cashier_id,
                'cashier_name': r.cashier.name,
                'class_code': r.student.class_code,
                'session_name': r.session.name,
                'term': r.term,
                'item_title': item.get('title'),
                'quantity': item.get('quantity'),
                'session_id': r.session_id
            })


    # ---------- Book receipts – each book item with proportional discount (unchanged) ----------
    for r in PrivateBookReceipt.query.filter_by(cashier_id=current_user.id).all():
        if not _check_date(r.created_at, start_date, end_date):
            continue
        if payment_method and r.payment_method != payment_method:
            continue
        if transaction_type and transaction_type != 'books':
            continue
        if class_code and r.student.class_code != class_code:
            continue
        if session_id and r.session_id != session_id:
            continue

        subtotal = float(r.total_amount or 0) + float(r.discount_amount or 0)
        total_discount = float(r.discount_amount or 0)

        for item in r.items:
            item_subtotal = float(item.get('total', 0))
            if (min_amount is not None and item_subtotal < min_amount) or (max_amount is not None and item_subtotal > max_amount):
                continue

            item_discount = 0
            if total_discount > 0 and subtotal > 0:
                discount_ratio = item_subtotal / subtotal
                item_discount = total_discount * discount_ratio
            item_total = item_subtotal - item_discount

            all_transactions.append({
                'id': f"{r.id}_{item.get('book_id')}",
                'receipt_number': r.receipt_number,
                'date': r.created_at,
                'type': 'books',
                'student_name': r.student.name,
                'student_phone': r.student.phone,
                'student_email': r.student.email or '',
                'amount': item_total,
                'subtotal': item_subtotal,
                'discount_amount': item_discount,
                'discount_reason': r.discount_reason,
                'discount_percentage': (item_discount / item_subtotal * 100) if item_subtotal > 0 else 0,
                'payment_method': r.payment_method,
                'cashier_id': r.cashier_id,
                'cashier_name': r.cashier.name,
                'class_code': r.student.class_code,
                'session_name': r.session.name,
                'term': r.term,
                'item_title': item.get('title'),
                'quantity': item.get('quantity'),
                'session_id': r.session_id
            })
            
    # ---------- Islamiyya receipts ----------
    for r in IslamiyyaPaymentReceipt.query.filter_by(cashier_id=current_user.id).all():
        if not _check_date(r.created_at, start_date, end_date):
            continue
        if payment_method and r.payment_method != payment_method:
            continue
        if transaction_type and transaction_type != 'islamiyya':
            continue
        if class_code and r.student.class_code != class_code:
            continue
        if session_id and r.session_id != session_id:
            continue

        amount = r.total_amount_float
        if (min_amount is not None and amount < min_amount) or (max_amount is not None and amount > max_amount):
            continue

        all_transactions.append({
            'id': r.id,
            'receipt_number': r.receipt_number,
            'date': r.created_at,
            'type': 'islamiyya',
            'student_name': r.student.name,
            'student_phone': r.student.phone,
            'student_email': r.student.email or '',
            'amount': amount,
            'subtotal': r.form_fee_charged_float + r.term_fee_charged_float,
            'discount_amount': r.discount_amount_float,
            'discount_reason': r.discount_reason,
            'discount_percentage': (r.discount_amount_float / (r.form_fee_charged_float + r.term_fee_charged_float) * 100) if (r.form_fee_charged_float + r.term_fee_charged_float) > 0 else 0,
            'payment_method': r.payment_method,
            'cashier_id': r.cashier_id,
            'cashier_name': r.cashier.name,
            'class_code': r.student.class_code,
            'session_name': r.session.name,
            'term': r.term,
            'item_title': 'Islamiyya Fees',
            'quantity': 1,
            'session_id': r.session_id
        })
        
    all_transactions.sort(key=lambda x: x['date'], reverse=True)
    total_amount = sum(t['amount'] for t in all_transactions)
    total_discount = sum(t['discount_amount'] for t in all_transactions)

    return jsonify({
        'transactions': [{**t, 'date': t['date'].isoformat()} for t in all_transactions],
        'total_count': len(all_transactions),
        'total_amount': total_amount,
        'total_discount': total_discount
    })

@app.route('/api/private/cashier/summary', methods=['GET'])
@login_required
@require_password_changed
@requires_roles('private', 'Cashier')
def get_private_cashier_summary():
    """Summary for the logged-in cashier (Total Revenue, Total Transactions, Revenue Today)."""

    start_date = request.args.get('start_date')
    end_date = request.args.get('end_date')
    payment_method = request.args.get('payment_method')
    transaction_type = request.args.get('transaction_type')
    class_code = request.args.get('class_code')
    min_amount = request.args.get('min_amount', type=float)
    max_amount = request.args.get('max_amount', type=float)
    session_id = request.args.get('session_id')
    
    # Local timezone (UTC+1 for Nigeria)
    now_local = datetime.now()
    today_start_local = datetime(now_local.year, now_local.month, now_local.day, 0, 0, 0)
    tomorrow_start_local = today_start_local + timedelta(days=1)

    total_revenue = 0.0
    total_transactions = 0
    revenue_today = 0.0

    # Enrolment split
    for r in PrivateEnrollmentReceipt.query.filter_by(cashier_id=current_user.id).all():
        if not _check_date(r.created_at, start_date, end_date):
            continue
        if payment_method and r.payment_method != payment_method:
            continue
        if transaction_type and transaction_type not in ['form', 'school_fees']:
            continue
        if class_code and r.class_code != class_code:
            continue
        if session_id and r.session_id != session_id:
            continue

        date_utc = r.created_at
        date_local = date_utc + timedelta(hours=1)
        is_today_local = (today_start_local <= date_local < tomorrow_start_local)

        subtotal_items = sum(float(item.get('amount', 0)) for item in r.items)
        total_discount = float(r.discount_amount) if r.discount_amount else 0
        total_additional = float(r.additional_amount) if r.additional_amount else 0

        for item in r.items:
            amount = float(item.get('amount', 0))
            item_name = item.get('name', '')
            txn_type = 'form' if item_name == 'Form Fee' else 'school_fees'
            if transaction_type and transaction_type != txn_type:
                continue
            if (min_amount is not None and amount < min_amount) or (max_amount is not None and amount > max_amount):
                continue

            item_discount = 0
            if total_discount > 0 and subtotal_items > 0:
                discount_ratio = amount / subtotal_items
                item_discount = total_discount * discount_ratio
            
            # Proportional additional fee
            item_additional = 0
            if total_additional > 0 and subtotal_items > 0:
                additional_ratio = amount / subtotal_items
                item_additional = total_additional * additional_ratio

            item_total = amount - item_discount + item_additional
            
            total_revenue += item_total
            total_transactions += 1
            if is_today_local:
                revenue_today += item_total

    # Standalone Form
    for r in PrivateFormReceipt.query.filter_by(cashier_id=current_user.id).all():
        if not _check_date(r.created_at, start_date, end_date):
            continue
        if payment_method and r.payment_method != payment_method:
            continue
        if transaction_type and transaction_type != 'form':
            continue
        if class_code and r.student.class_code != class_code:
            continue
        if session_id and r.session_id != session_id:
            continue

        date_utc = r.created_at
        date_local = date_utc + timedelta(hours=1)
        is_today_local = (today_start_local <= date_local < tomorrow_start_local)

        amount = float(r.total_amount_float)
        if (min_amount is not None and amount < min_amount) or (max_amount is not None and amount > max_amount):
            continue

        total_revenue += amount
        total_transactions += 1
        if is_today_local:
            revenue_today += amount

    # Uniform
    for r in PrivateUniformReceipt.query.filter_by(cashier_id=current_user.id).all():
        if not _check_date(r.created_at, start_date, end_date):
            continue
        if payment_method and r.payment_method != payment_method:
            continue
        if transaction_type and transaction_type != 'uniform':
            continue
        if class_code and r.student.class_code != class_code:
            continue
        if session_id and r.session_id != session_id:
            continue

        date_utc = r.created_at
        date_local = date_utc + timedelta(hours=1)
        is_today_local = (today_start_local <= date_local < tomorrow_start_local)

        subtotal = float(r.total_amount or 0) + float(r.discount_amount or 0)
        total_discount = float(r.discount_amount or 0)

        for item in r.items:
            item_subtotal = float(item.get('total', 0))
            if (min_amount is not None and item_subtotal < min_amount) or (max_amount is not None and item_subtotal > max_amount):
                continue

            item_discount = 0
            if total_discount > 0 and subtotal > 0:
                discount_ratio = item_subtotal / subtotal
                item_discount = total_discount * discount_ratio
            item_total = item_subtotal - item_discount

            total_revenue += item_total
            total_transactions += 1
            if is_today_local:
                revenue_today += item_total

    # Books
    for r in PrivateBookReceipt.query.filter_by(cashier_id=current_user.id).all():
        if not _check_date(r.created_at, start_date, end_date):
            continue
        if payment_method and r.payment_method != payment_method:
            continue
        if transaction_type and transaction_type != 'books':
            continue
        if class_code and r.student.class_code != class_code:
            continue
        if session_id and r.session_id != session_id:
            continue

        date_utc = r.created_at
        date_local = date_utc + timedelta(hours=1)
        is_today_local = (today_start_local <= date_local < tomorrow_start_local)

        subtotal = float(r.total_amount or 0) + float(r.discount_amount or 0)
        total_discount = float(r.discount_amount or 0)

        for item in r.items:
            item_subtotal = float(item.get('total', 0))
            if (min_amount is not None and item_subtotal < min_amount) or (max_amount is not None and item_subtotal > max_amount):
                continue

            item_discount = 0
            if total_discount > 0 and subtotal > 0:
                discount_ratio = item_subtotal / subtotal
                item_discount = total_discount * discount_ratio
            item_total = item_subtotal - item_discount

            total_revenue += item_total
            total_transactions += 1
            if is_today_local:
                revenue_today += item_total
                
    # ---------- Islamiyya receipts ----------
    for r in IslamiyyaPaymentReceipt.query.filter_by(cashier_id=current_user.id).all():
        if not _check_date(r.created_at, start_date, end_date):
            continue
        if payment_method and r.payment_method != payment_method:
            continue
        if transaction_type and transaction_type != 'islamiyya':
            continue
        if class_code and r.student.class_code != class_code:
            continue
        if session_id and r.session_id != session_id:
            continue

        date_utc = r.created_at
        date_local = date_utc + timedelta(hours=1)
        is_today_local = (today_start_local <= date_local < tomorrow_start_local)

        amount = r.total_amount_float
        if (min_amount is not None and amount < min_amount) or (max_amount is not None and amount > max_amount):
            continue

        total_revenue += amount
        total_transactions += 1
        if is_today_local:
            revenue_today += amount
            
    return jsonify({
        'total_revenue': total_revenue,
        'total_transactions': total_transactions,
        'revenue_today': revenue_today
    })



@app.route('/api/private/receipts', methods=['GET'])
@login_required
@require_password_changed
@requires_roles('private', 'Director')   # or 'Analyst'? Use appropriate role.
def get_private_receipts():
    """Return combined enrollment and book purchase receipts for private school."""
    receipt_type = request.args.get('type')          # 'enrolment', 'book', or None for both
    start_date = request.args.get('start_date')
    end_date = request.args.get('end_date')
    student_name = request.args.get('student_name')
    receipt_number = request.args.get('receipt_number')
    payment_method = request.args.get('payment_method')
    cashier_id = request.args.get('cashier_id')

    def filter_date(dt):
        if start_date:
            start = datetime.fromisoformat(start_date) if 'T' in start_date else datetime.fromisoformat(start_date + 'T00:00:00')
            if dt < start:
                return False
        if end_date:
            end = datetime.fromisoformat(end_date) if 'T' in end_date else datetime.fromisoformat(end_date + 'T23:59:59')
            if dt > end:
                return False
        return True

    receipts = []

    # 1. Enrollment receipts
    if receipt_type is None or receipt_type == 'enrolment':
        query = PrivateEnrollmentReceipt.query
        if cashier_id:
            query = query.filter_by(cashier_id=cashier_id)
        for r in query.all():
            if not filter_date(r.created_at):
                continue
            if payment_method and r.payment_method != payment_method:
                continue
            if receipt_number and receipt_number.lower() not in r.receipt_number.lower():
                continue
            if student_name and student_name.lower() not in r.student.name.lower():
                continue

            receipts.append({
                'id': r.id,
                'receipt_number': r.receipt_number,
                'type': 'enrolment',
                'student_id': r.student_id,
                'student_name': r.student.name,
                'student_phone': r.student.phone,
                'student_email': r.student.email or '',
                'date': r.created_at.isoformat(),
                'payment_method': r.payment_method,
                'cashier_name': r.cashier.name,
                'total_amount': float(r.total_amount),
                'discount_amount': float(r.discount_amount),
                'discount_reason': r.discount_reason,
                'details': {
                    'items': r.items,
                    'class_code': r.class_code,
                    'class_name': r.student.class_obj.name if r.student.class_obj else '',
                    'session_name': r.session.name,
                    'term': r.term,
                }
            })

    # 2. Book purchase receipts
    if receipt_type is None or receipt_type == 'book':
        query = PrivateBookReceipt.query
        if cashier_id:
            query = query.filter_by(cashier_id=cashier_id)
        for r in query.all():
            if not filter_date(r.created_at):
                continue
            if payment_method and r.payment_method != payment_method:
                continue
            if receipt_number and receipt_number.lower() not in r.receipt_number.lower():
                continue
            if student_name and student_name.lower() not in r.student.name.lower():
                continue

            receipts.append({
                'id': r.id,
                'receipt_number': r.receipt_number,
                'type': 'book',
                'student_id': r.student_id,
                'student_name': r.student.name,
                'student_phone': r.student.phone,
                'student_email': r.student.email or '',
                'date': r.created_at.isoformat(),
                'payment_method': r.payment_method,
                'cashier_name': r.cashier.name,
                'total_amount': float(r.total_amount),
                'discount_amount': float(r.discount_amount),
                'discount_reason': r.discount_reason,
                'details': {
                    'items': r.items,
                    'session_name': r.session.name,
                    'term': r.term,
                }
            })
            
    # 3. Islamiyya receipts
    if receipt_type is None or receipt_type == 'islamiyya':
        query = IslamiyyaPaymentReceipt.query
        if cashier_id:
            query = query.filter_by(cashier_id=cashier_id)
        for r in query.all():
            if not filter_date(r.created_at):
                continue
            if payment_method and r.payment_method != payment_method:
                continue
            if receipt_number and receipt_number.lower() not in r.receipt_number.lower():
                continue
            if student_name and student_name.lower() not in r.student.name.lower():
                continue

            receipts.append({
                'id': r.id,
                'receipt_number': r.receipt_number,
                'type': 'islamiyya',
                'student_id': r.student_id,
                'student_name': r.student.name,
                'student_phone': r.student.phone,
                'student_email': r.student.email or '',
                'date': r.created_at.isoformat(),
                'payment_method': r.payment_method,
                'cashier_name': r.cashier.name,
                'total_amount': r.total_amount_float,
                'discount_amount': r.discount_amount_float,
                'discount_reason': r.discount_reason,
                'details': {
                    'form_fee': r.form_fee_charged_float,
                    'term_fee': r.term_fee_charged_float,
                    'student_type': r.student_type,
                    'session_name': r.session.name,
                    'term': r.term,
                }
            })
            
    receipts.sort(key=lambda x: x['date'], reverse=True)
    return jsonify(receipts)
    

@app.route('/api/private/cashier/receipts', methods=['GET'])
@login_required
@require_password_changed
@requires_roles('private', 'Cashier')
def get_private_cashier_receipts():
    """Return combined enrollment and book purchase receipts for the logged-in cashier."""
    receipt_type = request.args.get('type')          # 'enrolment', 'book', or None for both
    start_date = request.args.get('start_date')
    end_date = request.args.get('end_date')
    student_name = request.args.get('student_name')
    receipt_number = request.args.get('receipt_number')
    payment_method = request.args.get('payment_method')

    def filter_date(dt):
        if start_date:
            start = datetime.fromisoformat(start_date) if 'T' in start_date else datetime.fromisoformat(start_date + 'T00:00:00')
            if dt < start:
                return False
        if end_date:
            end = datetime.fromisoformat(end_date) if 'T' in end_date else datetime.fromisoformat(end_date + 'T23:59:59')
            if dt > end:
                return False
        return True

    receipts = []

    # 1. Enrollment receipts for this cashier
    if receipt_type is None or receipt_type == 'enrolment':
        for r in PrivateEnrollmentReceipt.query.filter_by(cashier_id=current_user.id).all():
            if not filter_date(r.created_at):
                continue
            if payment_method and r.payment_method != payment_method:
                continue
            if receipt_number and receipt_number.lower() not in r.receipt_number.lower():
                continue
            if student_name and student_name.lower() not in r.student.name.lower():
                continue

            receipts.append({
                'id': r.id,
                'receipt_number': r.receipt_number,
                'type': 'enrolment',
                'student_id': r.student_id,
                'student_name': r.student.name,
                'student_phone': r.student.phone,
                'student_email': r.student.email or '',
                'date': r.created_at.isoformat(),
                'payment_method': r.payment_method,
                'cashier_name': r.cashier.name,
                'total_amount': float(r.total_amount),
                'discount_amount': float(r.discount_amount),
                'discount_reason': r.discount_reason,
                'details': {
                    'items': r.items,
                    'class_code': r.class_code,
                    'class_name': r.student.class_obj.name if r.student.class_obj else '',
                    'session_name': r.session.name,
                    'term': r.term,
                }
            })

    # 2. Book purchase receipts for this cashier
    if receipt_type is None or receipt_type == 'book':
        for r in PrivateBookReceipt.query.filter_by(cashier_id=current_user.id).all():
            if not filter_date(r.created_at):
                continue
            if payment_method and r.payment_method != payment_method:
                continue
            if receipt_number and receipt_number.lower() not in r.receipt_number.lower():
                continue
            if student_name and student_name.lower() not in r.student.name.lower():
                continue

            receipts.append({
                'id': r.id,
                'receipt_number': r.receipt_number,
                'type': 'book',
                'student_id': r.student_id,
                'student_name': r.student.name,
                'student_phone': r.student.phone,
                'student_email': r.student.email or '',
                'date': r.created_at.isoformat(),
                'payment_method': r.payment_method,
                'cashier_name': r.cashier.name,
                'total_amount': float(r.total_amount),
                'discount_amount': float(r.discount_amount),
                'discount_reason': r.discount_reason,
                'details': {
                    'items': r.items,
                    'session_name': r.session.name,
                    'term': r.term,
                }
            })
    # 3. Islamiyya receipts for this cashier
    if receipt_type is None or receipt_type == 'islamiyya':
        for r in IslamiyyaPaymentReceipt.query.filter_by(cashier_id=current_user.id).all():
            if not filter_date(r.created_at):
                continue
            if payment_method and r.payment_method != payment_method:
                continue
            if receipt_number and receipt_number.lower() not in r.receipt_number.lower():
                continue
            if student_name and student_name.lower() not in r.student.name.lower():
                continue

            receipts.append({
                'id': r.id,
                'receipt_number': r.receipt_number,
                'type': 'islamiyya',
                'student_id': r.student_id,
                'student_name': r.student.name,
                'student_phone': r.student.phone,
                'student_email': r.student.email or '',
                'date': r.created_at.isoformat(),
                'payment_method': r.payment_method,
                'cashier_name': r.cashier.name,
                'total_amount': r.total_amount_float,
                'discount_amount': r.discount_amount_float,
                'discount_reason': r.discount_reason,
                'details': {
                    'form_fee': r.form_fee_charged_float,
                    'term_fee': r.term_fee_charged_float,
                    'student_type': r.student_type,
                    'session_name': r.session.name,
                    'term': r.term,
                    'discount_percentage': (r.discount_amount_float / (r.form_fee_charged_float + r.term_fee_charged_float) * 100) if (r.form_fee_charged_float + r.term_fee_charged_float) > 0 else 0
                }
            })
            
    receipts.sort(key=lambda x: x['date'], reverse=True)
    return jsonify(receipts)

@app.route('/api/english/gate-passes', methods=['GET'])
@login_required
@require_password_changed
@requires_roles('english', 'Cashier')
def get_english_gate_passes():
    session_id = request.args.get('session_id')
    search = request.args.get('search', '').strip()

    if not session_id:
        return jsonify({'error': 'session_id is required'}), 400

    session = EnglishSession.query.filter_by(id=session_id, deleted=False).first()
    if not session:
        return jsonify({'error': 'Session not found'}), 404

    # Get all enrollment/class_change receipts (with pass_number) for this session
    receipts = EnglishRegistrationReceipt.query.filter(
        EnglishRegistrationReceipt.session_id == session_id,
        EnglishRegistrationReceipt.pass_number.isnot(None),
        EnglishRegistrationReceipt.receipt_type.in_(['enrollment', 'class_change'])
    ).order_by(EnglishRegistrationReceipt.pass_number).all()

    # Deduplicate by student – take earliest receipt per student
    student_map = {}
    for r in receipts:
        if r.student_id not in student_map:
            student_map[r.student_id] = r

    passes = []
    for r in student_map.values():
        student = r.student
        if search and search.lower() not in student.name.lower() and search.lower() not in (student.phone or '').lower():
            continue
        class_name = ''
        if student.class_code:
            english_class = EnglishClass.query.filter_by(code=student.class_code).first()
            class_name = english_class.name if english_class else student.class_code
        passes.append({
            'pass_no': r.pass_number,
            'student_id': student.id,
            'student_name': student.name,
            'student_phone': student.phone,
            'student_email': student.email or '',
            'receipt_number': r.receipt_number,
            'class_code': student.class_code,
            'class_name': class_name,
            'level_name': student.level_name or '-',
            'enrollment_date': r.created_at.isoformat()
        })

    return jsonify({
        'session': session.to_dict(),
        'passes': passes,
        'total': len(passes)
    })

@app.route('/api/english/gate-pass/<student_id>', methods=['GET'])
@login_required
@require_password_changed
@requires_roles('english', 'Cashier')
def get_english_gate_pass(student_id):
    session_id = request.args.get('session_id')
    if not session_id:
        return jsonify({'error': 'session_id is required'}), 400

    session = EnglishSession.query.filter_by(id=session_id, deleted=False).first()
    if not session:
        return jsonify({'error': 'Session not found'}), 404

    # Get the student's earliest enrollment/class_change receipt for this session
    receipt = EnglishRegistrationReceipt.query.filter(
        EnglishRegistrationReceipt.session_id == session_id,
        EnglishRegistrationReceipt.student_id == student_id,
        EnglishRegistrationReceipt.pass_number.isnot(None),
        EnglishRegistrationReceipt.receipt_type.in_(['enrollment', 'class_change'])
    ).order_by(EnglishRegistrationReceipt.created_at.asc()).first()

    if not receipt:
        return jsonify({'error': 'No enrollment found for this student in the selected session'}), 404

    student = receipt.student
    pass_no = receipt.pass_number
    if pass_no is None:
        return jsonify({'error': 'Invalid pass number'}), 500

    # Get class display name
    class_name = ''
    if student.class_code:
        english_class = EnglishClass.query.filter_by(code=student.class_code).first()
        class_name = english_class.name if english_class else student.class_code

    # Get time text and period from session
    time_text = session.time_info.time_text if session.time_info else 'Not Set'
    starting = session.period.starting.strftime('%d %b %Y') if session.period else 'Not Set'
    ending = session.period.ending.strftime('%d %b %Y') if session.period else 'Not Set'

    # Format receipt number for display: e.g., 0001NC9-11
    suffix = student.class_code
    # Use time_text first 5 chars or fallback
    time_suffix = time_text if time_text else 'T'
    formatted_receipt_no = f"{pass_no:04d}{suffix}{time_suffix}"

    # Level name: hyphen if missing
    level_display = student.level_name if student.level_name else '-'

    try:
        img_base64 = process_college_pass_english(
            receipt_no=formatted_receipt_no,
            name=student.name,
            class_name=class_name,
            level_name=level_display,
            time=time_text,
            phone=student.phone,
            starting=starting,
            ending=ending
        )
        return jsonify({
            'image': img_base64,
            'pass_no': pass_no,
            'student_name': student.name
        })
    except Exception as e:
        return jsonify({'error': f'Failed to generate gate pass: {str(e)}'}), 500
    
@app.route('/api/private/gate-passes', methods=['GET'])
@login_required
@require_password_changed
@requires_roles('private', 'Cashier')
def get_private_gate_passes():
    session_id = request.args.get('session_id')
    search = request.args.get('search', '').strip()

    if not session_id:
        return jsonify({'error': 'session_id is required'}), 400

    session = PrivateSession.query.filter_by(id=session_id, deleted=False).first()
    if not session:
        return jsonify({'error': 'Session not found'}), 404

    # Query all enrollment receipts for this session, ordered by pass_number
    receipts = PrivateEnrollmentReceipt.query.filter_by(session_id=session_id)\
        .order_by(PrivateEnrollmentReceipt.pass_number).all()

    # Optional filter by student (search)
    filtered = []
    for r in receipts:
        student = r.student
        if search and search.lower() not in student.name.lower() \
           and search.lower() not in (student.phone or '').lower() \
           and search.lower() not in (student.email or '').lower():
            continue
        filtered.append({
            'pass_no': r.pass_number,
            'student_id': student.id,
            'student_name': student.name,
            'student_phone': student.phone,
            'student_email': student.email or '',
            'receipt_number': r.receipt_number,
            'class_code': r.class_code,
            'class_name': student.class_obj.name if student.class_obj else '',
            'enrollment_date': r.created_at.isoformat()
        })

    return jsonify({
        'session': session.to_dict(),
        'passes': filtered,
        'total': len(filtered)
    })



@app.route('/api/private/gate-pass/<student_id>', methods=['GET'])
@login_required
@require_password_changed
@requires_roles('private', 'Cashier')
def get_private_gate_pass(student_id):
    """Generate and return a gate pass image (base64) for a student in a given session."""
    session_id = request.args.get('session_id')
    if not session_id:
        return jsonify({'error': 'session_id is required'}), 400
    
    session = PrivateSession.query.filter_by(id=session_id, deleted=False).first()
    if not session:
        return jsonify({'error': 'Session not found'}), 404
    
    # Get the student's enrollment receipt (earliest one – original pass number)
    receipt = PrivateEnrollmentReceipt.query.filter_by(
        student_id=student_id, session_id=session_id
    ).order_by(PrivateEnrollmentReceipt.created_at.asc()).first()
    if not receipt:
        return jsonify({'error': 'No enrollment found for this student in the selected session'}), 404
    
    # Use the stored pass_number (already sequential per session)
    pass_no = receipt.pass_number
    if pass_no is None:
        # Fallback for old data – compute on the fly (should not happen after migration)
        all_receipts = PrivateEnrollmentReceipt.query.filter_by(session_id=session_id)\
            .order_by(PrivateEnrollmentReceipt.created_at).all()
        unique_student_ids = []
        for idx, r in enumerate(all_receipts, start=1):
            if r.student_id not in unique_student_ids:
                unique_student_ids.append(r.student_id)
                if r.student_id == student_id:
                    pass_no = idx
                    break
        if pass_no is None:
            return jsonify({'error': 'Student not found in session passes'}), 404
    
    # Format term text (e.g., "1st", "2nd", "3rd")
    term_suffix = {1: 'st', 2: 'nd', 3: 'rd'}.get(session.term)
    term_str = f"{session.term}{term_suffix}".upper()
    session_name = session.name
    color_letter = session.pass_color  
    
    try:
        img_base64 = process_college_pass(term_str, session_name, pass_no, color_letter)
        return jsonify({
            'image': img_base64,
            'pass_no': pass_no,
            'student_name': receipt.student.name
        })
    except Exception as e:
        return jsonify({'error': 'Failed to generate gate pass'}), 500


@app.route('/api/private/uniforms', methods=['GET'])
@login_required
@require_password_changed
@requires_roles('private', 'InventoryManager')
def get_private_uniforms():
    uniforms = PrivateUniform.query.all()
    return jsonify([u.to_dict() for u in uniforms])


@app.route('/api/private/uniforms/add-uniform', methods=['POST'])
@login_required
@require_password_changed
@requires_roles('private', 'InventoryManager')
def add_private_uniform():
    data = request.get_json()
    title = data.get('title')
    price = data.get('price')
    quantity = data.get('quantity', 0)
    class_group = data.get('class_group')

    if not title or price is None or not class_group:
        return jsonify({'error': 'Title, price and class group are required'}), 400

    uniform = PrivateUniform(
        title=title,
        price=price,
        quantity=quantity,
        class_group=class_group
    )
    db.session.add(uniform)
    db.session.flush()   # to get uniform.id
    
    # 2. Create associated fee item (one‑time, not termly)
    fee_item = PrivateFeeItem(
        name=f"{title} ({class_group})",
        amount=price,
        termly=False
    )
    db.session.add(fee_item)
    db.session.flush()

    # 3. Assign fee item to all classes of this group
    class_codes = get_class_codes_for_group(class_group)
    for code in class_codes:
        assignment = PrivateFeeAssignment(fee_item_id=fee_item.id, class_code=code)
        db.session.add(assignment)

    # 4. Link uniform to fee item
    uniform.fee_item_id = fee_item.id
    
    db.session.commit()
    return jsonify({'success': True, 'uniform': uniform.to_dict()})


@app.route('/api/private/uniforms/edit-uniform/<uniform_id>', methods=['PUT'])
@login_required
@require_password_changed
@requires_roles('private', 'InventoryManager')
def edit_private_uniform(uniform_id):
    uniform = PrivateUniform.query.get_or_404(uniform_id)
    data = request.get_json()
    
    # Store old values for comparison
    old_group = uniform.class_group
    old_price = uniform.price
    old_title = uniform.title
    
    # Update uniform fields
    if 'title' in data:
        uniform.title = data['title']
    if 'price' in data:
        if data['price'] <= 0:
            return jsonify({'error': 'Price must be greater than 0'}), 400
        uniform.price = data['price']
    if 'quantity' in data:
        if data['quantity'] < 0:
            return jsonify({'error': 'Quantity cannot be negative'}), 400
        uniform.quantity = data['quantity']
    if 'class_group' in data:
        uniform.class_group = data['class_group']

    # Update linked fee item if it exists
    if uniform.fee_item_id:
        fee_item = PrivateFeeItem.query.get(uniform.fee_item_id)
        if fee_item:
            # Name might change if title or group changes
            new_name = f"{uniform.title} ({uniform.class_group})"
            if fee_item.name != new_name:
                fee_item.name = new_name
            # Amount might change
            if uniform.price != old_price:
                fee_item.amount = uniform.price

            # If class group changed, reassign classes
            if uniform.class_group != old_group:
                # Delete old assignments
                PrivateFeeAssignment.query.filter_by(fee_item_id=fee_item.id).delete()
                # Create new assignments for the new group
                new_codes = get_class_codes_for_group(uniform.class_group)
                for code in new_codes:
                    assignment = PrivateFeeAssignment(fee_item_id=fee_item.id, class_code=code)
                    db.session.add(assignment)

            db.session.add(fee_item)

    db.session.commit()
    return jsonify({'success': True, 'uniform': uniform.to_dict()})

@app.route('/api/private/uniforms/delete-uniform/<uniform_id>', methods=['DELETE'])
@login_required
@require_password_changed
@requires_roles('private', 'InventoryManager')
def delete_private_uniform(uniform_id):
    uniform = PrivateUniform.query.get(uniform_id)
    if not uniform:
        return jsonify({'error': 'Uniform not found'}), 404

    # Delete linked fee item and its assignments
    if uniform.fee_item_id:
        fee_item = PrivateFeeItem.query.get(uniform.fee_item_id)
        if fee_item:
            # Delete assignments first
            PrivateFeeAssignment.query.filter_by(fee_item_id=fee_item.id).delete()
            db.session.delete(fee_item)
            
    db.session.delete(uniform)
    db.session.commit()

    return jsonify({'success': True, 'message': 'Uniform deleted successfully'}), 200

@app.route('/api/private/purchase-uniforms', methods=['POST'])
@login_required
@require_password_changed
@requires_roles('private', 'Cashier')
def purchase_private_uniforms():
    data = request.get_json()

    student_id = data.get('student_id')
    if not student_id:
        return jsonify({'error': 'Student ID is required'}), 400

    session_id = data.get('session_id')
    if not session_id:
        return jsonify({'error': 'Session ID is required'}), 400

    items = data.get('items', [])
    if not items or not isinstance(items, list):
        return jsonify({'error': 'No items provided'}), 400

    payment_method = data.get('payment_method')
    if not payment_method:
        return jsonify({'error': 'Payment method is required'}), 400

    discount_amount = data.get('discount_amount', 0)
    discount_reason = data.get('discount_reason', '')

    # Validate session
    session = PrivateSession.query.filter_by(id=session_id, deleted=False).first()
    if not session:
        return jsonify({'error': 'Invalid or deleted session'}), 400

    # Validate student
    student = PrivateStudent.query.get(student_id)
    if not student:
        return jsonify({'error': 'Student not found'}), 404

    if discount_amount < 0:
        return jsonify({'error': 'Discount amount cannot be negative'}), 400

    try:
        subtotal = 0
        purchase_items = []

        for item in items:
            uniform = PrivateUniform.query.get(item.get('uniform_id'))
            if not uniform:
                return jsonify({'error': 'Uniform not found'}), 404

            qty = item.get('quantity')
            if not qty or qty <= 0:
                return jsonify({'error': 'Invalid quantity'}), 400
            if uniform.quantity < qty:
                return jsonify({'error': f'Not enough stock for {uniform.title}'}), 400

            item_total = uniform.price * qty
            subtotal += item_total

            purchase_items.append({
                'uniform_id': uniform.id,
                'title': uniform.title,
                'quantity': qty,
                'price': float(uniform.price),
                'class_group': uniform.class_group,
                'total': float(item_total)
            })

            uniform.quantity -= qty

        if discount_amount > subtotal:
            return jsonify({
                'error': f'Discount amount (₦{discount_amount:,.2f}) cannot exceed subtotal (₦{subtotal:,.2f})',
                'max_allowed_discount': subtotal
            }), 400

        total_amount = subtotal - discount_amount

        discount_percentage = (discount_amount / subtotal * 100) if subtotal > 0 else 0
        if discount_percentage > 0 and not discount_reason:
            return jsonify({'error': 'Discount reason required'}), 400

        receipt_number = generate_receipt_number()

        receipt = PrivateUniformReceipt(
            receipt_number=receipt_number,
            student_id=student.id,
            session_id=session.id,
            term=session.term,
            total_amount=total_amount,
            discount_amount=discount_amount,
            discount_reason=discount_reason,
            payment_method=payment_method,
            cashier_id=current_user.id,
            items=purchase_items
        )

        db.session.add(receipt)
        db.session.commit()

        return jsonify({
            'success': True,
            'receipt': receipt.to_dict()
        })

    except Exception as e:
        db.session.rollback()
        return jsonify({'error': f'Purchase failed: {str(e)}'}), 500

@app.route('/api/private/forms', methods=['GET'])
@login_required
def get_private_forms():
    forms = PrivateFormStock.query.all()
    return jsonify([f.to_dict() for f in forms])


@app.route('/api/private/forms/add-form', methods=['POST'])
@login_required
@require_password_changed
@requires_roles('private', 'InventoryManager')
def add_private_form():
    data = request.get_json()
    class_group = data.get('class_group')
    price = data.get('price')
    quantity = data.get('quantity', 0)

    if not class_group or price is None:
        return jsonify({'error': 'Class group and price are required'}), 400
    if price <= 0:
        return jsonify({'error': 'Price must be greater than 0'}), 400
    if quantity < 0:
        return jsonify({'error': 'Quantity cannot be negative'}), 400

    form = PrivateFormStock(
        class_group=class_group,
        price=price,
        quantity=quantity
    )
    db.session.add(form)
    db.session.flush()
    
    fee_item = PrivateFeeItem(
        name=f"Admission Form ({class_group})",
        amount=price,
        termly=False           # session / one‑time
    )
    db.session.add(fee_item)
    db.session.flush()  
    
    # 3. Assign all classes of this group to the fee item
    class_codes = get_class_codes_for_group(class_group)
    for code in class_codes:
        assignment = PrivateFeeAssignment(fee_item_id=fee_item.id, class_code=code)
        db.session.add(assignment)

    # 4. Link form to fee item
    form.fee_item_id = fee_item.id
    
    db.session.commit()
    return jsonify({'success': True, 'form': form.to_dict()})


@app.route('/api/private/forms/edit-form/<form_id>', methods=['PUT'])
@login_required
@require_password_changed
@requires_roles('private', 'InventoryManager')
def edit_private_form(form_id):
    form = PrivateFormStock.query.get_or_404(form_id)
    data = request.get_json()
    
    # Store original values for comparison
    old_group = form.class_group
    old_price = form.price

    if 'class_group' in data:
        form.class_group = data['class_group']
    if 'price' in data:
        if data['price'] <= 0:
            return jsonify({'error': 'Price must be greater than 0'}), 400
        form.price = data['price']
    if 'quantity' in data:
        if data['quantity'] < 0:
            return jsonify({'error': 'Quantity cannot be negative'}), 400
        form.quantity = data['quantity']
        
    # If the form has a linked fee item, update it
    if form.fee_item_id:
        fee_item = PrivateFeeItem.query.get(form.fee_item_id)
        if fee_item:
            # Update name if group changed
            if form.class_group != old_group:
                fee_item.name = f"Admission Form ({form.class_group})"
            # Update amount if price changed
            if form.price != old_price:
                fee_item.amount = form.price

            # If group changed, reassign classes
            if form.class_group != old_group:
                # Delete old assignments
                PrivateFeeAssignment.query.filter_by(fee_item_id=fee_item.id).delete()
                # Create new assignments for the new group
                new_codes = get_class_codes_for_group(form.class_group)
                for code in new_codes:
                    assignment = PrivateFeeAssignment(fee_item_id=fee_item.id, class_code=code)
                    db.session.add(assignment)

            db.session.add(fee_item)

    db.session.commit()
    return jsonify({'success': True, 'form': form.to_dict()})


@app.route('/api/private/forms/delete-form/<form_id>', methods=['DELETE'])
@login_required
@require_password_changed
@requires_roles('private', 'InventoryManager')
def delete_private_form(form_id):
    form = PrivateFormStock.query.get_or_404(form_id)
    if form.receipts:
        return jsonify({'error': 'Cannot delete form with existing sales receipts'}), 400
    
    # If there is a linked fee item, delete it first
    if form.fee_item_id:
        fee_item = PrivateFeeItem.query.get(form.fee_item_id)
        if fee_item:
            # Manually delete assignments
            PrivateFeeAssignment.query.filter_by(fee_item_id=fee_item.id).delete()
             # Delete the fee item
            db.session.delete(fee_item) 
            
    db.session.delete(form)
    db.session.commit()
    return jsonify({'success': True})


@app.route('/api/private/purchase-form', methods=['POST'])
@login_required
@require_password_changed
@requires_roles('private', 'Cashier')
def purchase_private_form():
    data = request.get_json()

    # Student info (either existing or new)
    student_id = data.get('student_id')
    new_student_data = data.get('new_student_data')

    session_id = data.get('session_id')
    form_id = data.get('form_id')
    payment_method = data.get('payment_method')
    discount_amount = data.get('discount_amount', 0)
    discount_reason = data.get('discount_reason', '')

    if not session_id:
        return jsonify({'error': 'Session is required'}), 400
    if not form_id:
        return jsonify({'error': 'Form is required'}), 400
    if not payment_method:
        return jsonify({'error': 'Payment method is required'}), 400
    if discount_amount < 0:
        return jsonify({'error': 'Discount cannot be negative'}), 400

    # Validate session
    session = PrivateSession.query.filter_by(id=session_id, deleted=False).first()
    if not session:
        return jsonify({'error': 'Invalid or deleted session'}), 400

    # Validate form stock
    form = PrivateFormStock.query.get(form_id)
    if not form:
        return jsonify({'error': 'Form not found'}), 404
    if form.quantity < 1:
        return jsonify({'error': 'Form out of stock'}), 400

    # Determine student
    student = None
    if student_id:
        student = PrivateStudent.query.get(student_id)
        if not student:
            return jsonify({'error': 'Student not found'}), 404
    elif new_student_data:
        name = (new_student_data.get('name') or '').strip()
        phone = (new_student_data.get('phone') or '').strip()
        email = (new_student_data.get('email') or '').strip()
        if not name or not phone:
            return jsonify({'error': 'Name and phone are required for new student'}), 400
        if email and not is_valid_email(email):
            return jsonify({'error': 'Invalid email address'}), 400

        student = PrivateStudent(
            name=name,
            phone=phone,
            email=email if email else None,
            class_code=None   # No class for form-only purchase
        )
        db.session.add(student)
        db.session.flush()
    else:
        return jsonify({'error': 'Either select an existing student or provide new student details'}), 400

    # Calculate total
    subtotal = form.price
    if discount_amount > subtotal:
        return jsonify({
            'error': f'Discount amount (₦{discount_amount:,.2f}) cannot exceed price (₦{subtotal:,.2f})',
            'max_allowed_discount': subtotal
        }), 400

    discount_percentage = (discount_amount / subtotal * 100) if subtotal > 0 else 0
    if discount_percentage > 0 and not discount_reason:
        return jsonify({'error': 'Discount reason required'}), 400

    total_amount = subtotal - discount_amount

    # Deduct stock
    form.quantity -= 1

    # Create receipt
    receipt_number = generate_receipt_number()
    receipt = PrivateFormReceipt(
        receipt_number=receipt_number,
        student_id=student.id,
        session_id=session.id,
        term=session.term,
        form_id=form.id,
        form_price=form.price,
        form_class_group=form.class_group,
        discount_amount=discount_amount,
        discount_reason=discount_reason,
        total_amount=total_amount,
        payment_method=payment_method,
        cashier_id=current_user.id
    )
    db.session.add(receipt)
    db.session.commit()

    return jsonify({
        'success': True,
        'receipt': receipt.to_dict(),
        'student': student.to_dict()
    })




@app.route('/api/private/bulk-enroll-template', methods=['GET'])
@login_required
@require_password_changed
def download_bulk_enroll_template():
    """Download Excel template for bulk enrollment"""
    wb = Workbook()
    ws = wb.active
    ws.title = "Bulk Enrollment"
    
    # Headers
    headers = ['name', 'phone', 'email', 'class_code']
    ws.append(headers)
    
    # Example rows
    examples = [
        ['John Doe', '08012345678', 'john@example.com', 'N1'],
        ['Jane Smith', '08098765432', '', 'P3'],
    ]
    for row in examples:
        ws.append(row)
    
    # Add instruction notes
    ws['F1'] = 'Valid class codes:'
    ws['F2'] = 'PN (Pre-Nursery)'
    ws['F3'] = 'N1 (Nursery 1)'
    ws['F4'] = 'N2 (Nursery 2)'
    ws['F5'] = 'P1, P2, P3, P4, P5 (Primary 1-5)'
    ws['F6'] = 'JSS1, JSS2, JSS3 (JSS 1-3)'
    ws['F7'] = 'SS1, SS2, SS3 (SSS 1-3)'
    ws['F9'] = 'Note: Email is optional. Phone numbers should be 11 digits.'
    
    # Auto-size columns
    for col in ws.columns:
        max_length = 0
        col_letter = col[0].column_letter
        for cell in col:
            try:
                if cell.value and len(str(cell.value)) > max_length:
                    max_length = len(str(cell.value))
            except:
                pass
        adjusted_width = min(max_length + 2, 30)
        ws.column_dimensions[col_letter].width = adjusted_width
    
    output = BytesIO()
    wb.save(output)
    output.seek(0)
    
    return Response(
        output.getvalue(),
        mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        headers={
            'Content-Disposition': 'attachment; filename="private_bulk_enrollment_template.xlsx"'
        }
    )


@app.route('/api/private/bulk-enroll', methods=['POST'])
@login_required
@require_password_changed
@requires_roles('private', 'Cashier')
def bulk_enroll_private_students():
    """Upload Excel file, validate, preview, and bulk enroll"""
    
    if 'file' not in request.files:
        return jsonify({'error': 'No file uploaded'}), 400
    
    file = request.files['file']
    if file.filename == '':
        return jsonify({'error': 'Empty filename'}), 400
    
    if not file.filename.lower().endswith(('.xlsx', '.xls')):
        return jsonify({'error': 'File must be Excel (.xlsx or .xls)'}), 400
    
    try:
        # Read Excel file
        df = pd.read_excel(file, engine='openpyxl')
        
        # Required columns
        required_columns = ['name', 'phone', 'class_code']
        for col in required_columns:
            if col not in df.columns:
                return jsonify({'error': f'Missing column: {col}'}), 400
        
        preview_data = []
        errors = []
        valid_rows = []
        
        for idx, row in df.iterrows():
            row_num = idx + 2  # Excel row number (1-indexed + header row)
            
            # Strip spaces and handle NaN
            name = str(row.get('name', '')).strip() if pd.notna(row.get('name')) else ''
            phone = str(row.get('phone', '')).strip() if pd.notna(row.get('phone')) else ''
            email = str(row.get('email', '')).strip() if pd.notna(row.get('email')) else ''
            class_short = str(row.get('class_code', '')).strip().upper() if pd.notna(row.get('class_code')) else ''
            
            # Validation
            row_errors = []
            
            if not name:
                row_errors.append('Name is required')
            if not phone:
                row_errors.append('Phone is required')
            if email and not is_valid_email(email):
                row_errors.append('Invalid email address')
            if not class_short:
                row_errors.append('Class code is required')
            elif class_short not in SHORT_CODE_TO_CLASS_CODE:
                row_errors.append(f'Invalid class code: {class_short}. Valid codes: {", ".join(SHORT_CODE_TO_CLASS_CODE.keys())}')
            
            # Get actual class name for preview
            actual_class_name = ''
            if class_short in SHORT_CODE_TO_CLASS_CODE:
                actual_class_name = get_class_display_name_from_code(SHORT_CODE_TO_CLASS_CODE[class_short])
            
            if row_errors:
                errors.append({
                    'row': row_num,
                    'name': name or '(empty)',
                    'phone': phone or '(empty)',
                    'email': email or '(empty)',
                    'class_code_entered': class_short or '(empty)',
                    'errors': row_errors
                })
            else:
                valid_rows.append({
                    'name': name,
                    'phone': phone,
                    'email': email if email else None,
                    'class_code': SHORT_CODE_TO_CLASS_CODE[class_short],
                    'class_short': class_short,
                    'actual_class_name': actual_class_name
                })
                preview_data.append({
                    'name': name,
                    'phone': phone,
                    'email': email,
                    'class_code_entered': class_short,
                    'actual_class_name': actual_class_name
                })
        
        # If there are errors, return them without saving
        if errors:
            return jsonify({
                'success': False,
                'errors': errors,
                'message': f'{len(errors)} row(s) have errors. Please correct and re-upload.'
            }), 400
        
        # Check if this is a preview request
        is_preview = request.args.get('preview', 'false').lower() == 'true'
        if is_preview:
            return jsonify({
                'success': True,
                'preview': preview_data,
                'total': len(valid_rows)
            })
        
        # Otherwise, commit the bulk insert
        students_to_add = []
        for student_data in valid_rows:
            student = PrivateStudent(
                name=student_data['name'],
                phone=student_data['phone'],
                email=student_data['email'],
                class_code=student_data['class_code']
            )
            students_to_add.append(student)
        
        db.session.add_all(students_to_add)
        db.session.commit()
        
        return jsonify({
            'success': True,
            'message': f'Successfully enrolled {len(students_to_add)} student(s)',
            'enrolled': len(students_to_add)
        })
        
    except Exception as e:
        db.session.rollback()
        return jsonify({'error': f'Failed to process file: {str(e)}'}), 500



@app.route('/api/islamiyya/fees', methods=['GET'])
@login_required
@require_password_changed
@requires_roles('private', 'Cashier')   # Cashier or Director can view
def get_islamiyya_fees():
    setting = IslamiyyaFeeSetting.query.first()
    if not setting:
        # Create default if not exists
        setting = IslamiyyaFeeSetting(form_fee=0, term_fee=0)
        db.session.add(setting)
        db.session.commit()
    return jsonify({
        'form_fee': setting.form_fee_float,
        'term_fee': setting.term_fee_float
    })
    
@app.route('/api/islamiyya/edit-fees', methods=['PUT'])
@login_required
@require_password_changed
@requires_roles('private', 'InventoryManager')
def edit_islamiyya_fees():
    data = request.get_json()
    form_fee = data.get('form_fee')
    term_fee = data.get('term_fee')
    
    if form_fee is None or term_fee is None:
        return jsonify({'error': 'Both form_fee and term_fee are required'}), 400
    
    try:
        form_fee = float(form_fee)
        term_fee = float(term_fee)
        if form_fee < 0 or term_fee < 0:
            return jsonify({'error': 'Fees cannot be negative'}), 400
    except (TypeError, ValueError):
        return jsonify({'error': 'Invalid fee amount'}), 400
    
    setting = IslamiyyaFeeSetting.query.first()
    if not setting:
        setting = IslamiyyaFeeSetting()
        db.session.add(setting)
    
    setting.form_fee_float = form_fee
    setting.term_fee_float = term_fee
    db.session.commit()
    
    return jsonify({'success': True, 'form_fee': form_fee, 'term_fee': term_fee})


@app.route('/api/islamiyya/pay', methods=['POST'])
@login_required
@require_password_changed
@requires_roles('private', 'Cashier')
def pay_islamiyya_fees():
    data = request.get_json()
    
    student_id = data.get('student_id')
    session_id = data.get('session_id')
    student_type = data.get('student_type')   # 'fresh' or 'returning'
    payment_method = data.get('payment_method')
    discount_amount = data.get('discount_amount', 0)
    discount_reason = data.get('discount_reason', '')
    
    # Validation
    if not student_id:
        return jsonify({'error': 'Student ID is required'}), 400
    if not session_id:
        return jsonify({'error': 'Session ID is required'}), 400
    if student_type not in ['fresh', 'returning']:
        return jsonify({'error': 'Student type must be "fresh" or "returning"'}), 400
    if not payment_method:
        return jsonify({'error': 'Payment method is required'}), 400
    if discount_amount < 0:
        return jsonify({'error': 'Discount cannot be negative'}), 400
    
    # Fetch student
    student = PrivateStudent.query.get(student_id)
    if not student:
        return jsonify({'error': 'Student not found'}), 404
    
    # Fetch session
    session = PrivateSession.query.filter_by(id=session_id, deleted=False).first()
    if not session:
        return jsonify({'error': 'Invalid or deleted session'}), 400
    
    # Fetch current fee settings
    setting = IslamiyyaFeeSetting.query.first()
    if not setting:
        setting = IslamiyyaFeeSetting(form_fee=0, term_fee=0)
        db.session.add(setting)
        db.session.commit()
    
    # Calculate charges based on student type
    form_fee_charged = 0
    term_fee_charged = 0
    if student_type == 'fresh':
        form_fee_charged = setting.form_fee_float
        term_fee_charged = setting.term_fee_float
    else:  # returning
        term_fee_charged = setting.term_fee_float
    
    subtotal = form_fee_charged + term_fee_charged
    
    # Validate discount
    if discount_amount > subtotal:
        return jsonify({
            'error': f'Discount amount (₦{discount_amount:,.2f}) cannot exceed subtotal (₦{subtotal:,.2f})',
            'max_allowed_discount': subtotal
        }), 400
    
    discount_percentage = (discount_amount / subtotal * 100) if subtotal > 0 else 0
    if discount_percentage > 0 and not discount_reason:
        return jsonify({'error': 'Discount reason required'}), 400
    
    total = subtotal - discount_amount
    
    # Generate receipt number
    receipt_number = generate_receipt_number()  # e.g., RCP-XXXXXXX
    
    # Create receipt
    receipt = IslamiyyaPaymentReceipt(
        receipt_number=receipt_number,
        student_id=student.id,
        session_id=session.id,
        term=session.term,
        student_type=student_type,
        form_fee_charged=form_fee_charged,
        term_fee_charged=term_fee_charged,
        discount_amount=discount_amount,
        discount_reason=discount_reason,
        total_amount=total,
        payment_method=payment_method,
        cashier_id=current_user.id
    )
    
    db.session.add(receipt)
    db.session.commit()
    
    return jsonify({
        'success': True,
        'receipt': receipt.to_dict()
    })
    
@app.route('/api/islamiyya/receipts', methods=['GET'])
@login_required
@require_password_changed
@requires_roles('private', 'Cashier', 'Analyst')
def get_islamiyya_receipts():
    # Optional filters: student_id, session_id, start_date, end_date
    student_id = request.args.get('student_id')
    session_id = request.args.get('session_id')
    start_date = request.args.get('start_date')
    end_date = request.args.get('end_date')
    
    query = IslamiyyaPaymentReceipt.query
    if student_id:
        query = query.filter_by(student_id=student_id)
    if session_id:
        query = query.filter_by(session_id=session_id)
    if start_date:
        start = datetime.fromisoformat(start_date)
        query = query.filter(IslamiyyaPaymentReceipt.created_at >= start)
    if end_date:
        end = datetime.fromisoformat(end_date)
        query = query.filter(IslamiyyaPaymentReceipt.created_at <= end)
    
    receipts = query.order_by(IslamiyyaPaymentReceipt.created_at.desc()).all()
    return jsonify([r.to_dict() for r in receipts])



if __name__ == '__main__':
    with app.app_context():
        initialize_database()
        
    app.run(debug=True, port=5006)