from flask import render_template, request, jsonify, redirect, url_for, flash, session
from flask_login import login_user, logout_user, login_required, current_user
from werkzeug.security import generate_password_hash, check_password_hash
from app import app, db
from models import Member, Currency, Contribution, BigCollectionEvent, BigCollectionParticipation, Group, LoanRequest, ContributionRequest
from datetime import datetime, date, timedelta
from sqlalchemy import func, text
from decimal import Decimal
import os
import json
import re


@app.route('/')
def index():
    """Landing page"""
    return render_template('index.html')


@app.route('/admin/login', methods=['GET', 'POST'])
def admin_login():
    """Admin login page with group selection"""
    if request.method == 'POST':
        username = request.form.get('username')
        password = request.form.get('password')
        selected_group_code = request.form.get('group_code')
        
        # Require group selection
        if not selected_group_code:
            flash('Please select a group to continue', 'error')
            groups = Group.query.filter_by(is_active=True).order_by(Group.name).all()
            return render_template('admin/login.html', groups=groups)
        
        member = Member.query.filter_by(username=username, is_admin=True).first()
        if member and check_password_hash(member.password_hash, password):
            login_user(member)
            
            # Store selected group in session
            session['selected_group_code'] = selected_group_code
            group = Group.query.filter_by(group_code=selected_group_code).first()
            if group:
                return redirect(url_for('group_admin_dashboard', group_code=selected_group_code))
            else:
                flash('Selected group not found', 'error')
        else:
            flash('Invalid credentials or not an admin user', 'error')
    
    # Get all groups for selection dropdown
    groups = Group.query.filter_by(is_active=True).order_by(Group.name).all()
    return render_template('admin/login.html', groups=groups)


@app.route('/superadmin/login', methods=['GET', 'POST'])
def superadmin_login():
    """Superadmin login page"""
    if request.method == 'POST':
        username = request.form.get('username')
        password = request.form.get('password')
        
        member = Member.query.filter_by(username=username, is_superadmin=True).first()
        if member and check_password_hash(member.password_hash, password):
            login_user(member)
            return redirect(url_for('superadmin_dashboard'))
        else:
            flash('Invalid credentials or not a superadmin user', 'error')
    
    return render_template('superadmin/login.html')


@app.route('/member/login', methods=['GET', 'POST'])
def member_login():
    """Member login page with group selection"""
    if request.method == 'POST':
        username = request.form.get('username')
        password = request.form.get('password')
        selected_group_code = request.form.get('group_code')
        
        # Require group selection
        if not selected_group_code:
            flash('Please select a group to continue', 'error')
            groups = Group.query.filter_by(is_active=True).order_by(Group.name).all()
            return render_template('member/login.html', groups=groups)
        
        # Find member by username
        member = Member.query.filter_by(username=username, is_admin=False).first()
        if member and check_password_hash(member.password_hash, password):
            # Check if member belongs to selected group
            if member.group and member.group.group_code == selected_group_code:
                login_user(member)
                session['member_group_code'] = selected_group_code
                return redirect(url_for('member_dashboard'))
            else:
                flash('You are not a member of the selected group', 'error')
        else:
            flash('Invalid credentials or not a member account', 'error')
    
    # Get all groups for selection dropdown
    groups = Group.query.filter_by(is_active=True).order_by(Group.name).all()
    return render_template('member/login.html', groups=groups)


@app.route('/admin/dashboard')
@login_required
def admin_dashboard():
    """Admin dashboard - redirects to group selection or specific group"""
    if not current_user.is_admin:
        flash('Access denied', 'error')
        return redirect(url_for('index'))
    
    # If superadmin, show group selection if multiple groups exist
    if current_user.is_superadmin:
        groups = Group.query.all()
        if len(groups) > 1:
            return redirect(url_for('group_select'))
        elif len(groups) == 1:
            return redirect(url_for('group_admin_dashboard', group_code=groups[0].group_code))
        else:
            # No groups yet, redirect to create one
            flash('No groups found. Create your first group to get started.', 'info')
            return redirect(url_for('superadmin_add_group'))
    
    # If regular admin with assigned group, go directly to group dashboard
    if current_user.group_id:
        group = Group.query.get(current_user.group_id)
        if group:
            return redirect(url_for('group_admin_dashboard', group_code=group.group_code))
    
    # Admin without group assignment
    flash('You are not assigned to any group. Contact the superadmin for group assignment.', 'warning')
    return render_template('admin/no_group.html')


@app.route('/member/dashboard')
@login_required
def member_dashboard():
    """Enhanced member dashboard with comprehensive data and features"""
    if current_user.is_admin:
        flash('Please use the admin portal for admin access', 'error')
        return redirect(url_for('admin_login'))
    
    # Get member's group for data filtering
    member_group = current_user.group
    if not member_group:
        flash('You are not assigned to any group. Contact your administrator.', 'error')
        return redirect(url_for('member_login'))
    
    # Get member's contributions (only from their group)
    my_contributions = Contribution.query.filter_by(
        member_id=current_user.id,
        group_id=member_group.id
    ).order_by(Contribution.created_at.desc()).all()
    total_contributed = sum(c.amount for c in my_contributions)
    
    # Monthly contributions breakdown
    monthly_contributions = Contribution.query.filter_by(
        member_id=current_user.id,
        group_id=member_group.id,
        contribution_type='monthly'
    ).order_by(Contribution.contribution_month.desc()).all()
    
    # Special contributions breakdown
    special_contributions = Contribution.query.filter_by(
        member_id=current_user.id,
        group_id=member_group.id,
        contribution_type='special'
    ).order_by(Contribution.created_at.desc()).all()
    
    # Calculate contribution totals by type
    monthly_total = db.session.query(db.func.sum(Contribution.amount)).filter_by(
        member_id=current_user.id,
        group_id=member_group.id,
        contribution_type='monthly'
    ).scalar() or 0
    
    special_total = db.session.query(db.func.sum(Contribution.amount)).filter_by(
        member_id=current_user.id,
        group_id=member_group.id,
        contribution_type='special'
    ).scalar() or 0
    
    penalty_total = db.session.query(db.func.sum(Contribution.amount)).filter_by(
        member_id=current_user.id,
        group_id=member_group.id,
        contribution_type='penalty'
    ).scalar() or 0
    
    bonus_total = db.session.query(db.func.sum(Contribution.amount)).filter_by(
        member_id=current_user.id,
        group_id=member_group.id,
        contribution_type='bonus'
    ).scalar() or 0
    
    # Get group statistics (only for this member's group)
    total_members = Member.query.filter_by(
        group_id=member_group.id,
        is_active=True,
        is_admin=False
    ).count()
    total_group_deposits = db.session.query(db.func.sum(Contribution.amount)).filter_by(
        group_id=member_group.id
    ).scalar() or 0
    
    # Get group totals by contribution type
    group_monthly_total = db.session.query(db.func.sum(Contribution.amount)).filter_by(
        group_id=member_group.id,
        contribution_type='monthly'
    ).scalar() or 0
    
    group_special_total = db.session.query(db.func.sum(Contribution.amount)).filter_by(
        group_id=member_group.id,
        contribution_type='special'
    ).scalar() or 0
    
    group_penalty_total = db.session.query(db.func.sum(Contribution.amount)).filter_by(
        group_id=member_group.id,
        contribution_type='penalty'
    ).scalar() or 0
    
    group_bonus_total = db.session.query(db.func.sum(Contribution.amount)).filter_by(
        group_id=member_group.id,
        contribution_type='bonus'
    ).scalar() or 0
    
    # Get active big collection events (only for this group)
    active_events = BigCollectionEvent.query.filter_by(
        group_id=member_group.id,
        is_active=True
    ).all()
    
    # Check monthly payment status
    current_month = date.today().replace(day=1)  # First day of current month
    monthly_payment = Contribution.query.filter_by(
        member_id=current_user.id,
        group_id=member_group.id,
        contribution_type='monthly',
        contribution_month=current_month
    ).first()
    monthly_payment_due = monthly_payment is None
    
    # Recent group activity
    recent_group_contributions = Contribution.query.join(
        Member, Member.id == Contribution.member_id
    ).filter(
        Contribution.group_id == member_group.id
    ).order_by(Contribution.created_at.desc()).limit(10).all()
    
    # Member ranking by contribution
    try:
        member_rankings_raw = db.session.query(
            Member.id,
            Member.full_name,
            Member.custom_id,
            db.func.sum(Contribution.amount).label('total_amount')
        ).join(Contribution, Member.id == Contribution.member_id).filter(
            Member.group_id == member_group.id,
            Member.is_admin == False,
            Member.is_active == True,
            Contribution.group_id == member_group.id
        ).group_by(Member.id, Member.full_name, Member.custom_id).order_by(db.func.sum(Contribution.amount).desc()).limit(10).all()
        
        # Convert to proper objects
        member_rankings = []
        for row in member_rankings_raw:
            ranking = type('Ranking', (), {
                'id': row.id,
                'full_name': row.full_name,
                'custom_id': row.custom_id,
                'total_amount': float(row.total_amount) if row.total_amount else 0
            })()
            member_rankings.append(ranking)
    except Exception as e:
        member_rankings = []
    
    # Monthly contribution stats for chart (using PostgreSQL date_trunc)
    try:
        monthly_stats_raw = db.session.query(
            db.func.date_trunc('month', Contribution.created_at).label('month'),
            db.func.sum(Contribution.amount).label('total')
        ).filter_by(
            member_id=current_user.id,
            group_id=member_group.id
        ).group_by(db.func.date_trunc('month', Contribution.created_at)).order_by('month').all()
        
        # Convert to JSON serializable format
        monthly_stats = []
        for row in monthly_stats_raw:
            monthly_stats.append({
                'month': row.month.isoformat() if row.month else None,
                'total': float(row.total) if row.total else 0
            })
    except Exception as e:
        # Fallback for non-PostgreSQL databases
        monthly_stats = []
    
    # Upcoming events
    upcoming_events = BigCollectionEvent.query.filter(
        BigCollectionEvent.group_id == member_group.id,
        BigCollectionEvent.is_active == True,
        BigCollectionEvent.end_date >= date.today()
    ).order_by(BigCollectionEvent.start_date).all()
    
    return render_template('member/dashboard.html',
                         my_contributions=my_contributions,
                         monthly_contributions=monthly_contributions,
                         special_contributions=special_contributions,
                         total_contributed=total_contributed,
                         monthly_total=monthly_total,
                         special_total=special_total,
                         penalty_total=penalty_total,
                         bonus_total=bonus_total,
                         total_members=total_members,
                         total_group_deposits=total_group_deposits,
                         group_monthly_total=group_monthly_total,
                         group_special_total=group_special_total,
                         group_penalty_total=group_penalty_total,
                         group_bonus_total=group_bonus_total,
                         active_events=active_events,
                         upcoming_events=upcoming_events,
                         monthly_payment_due=monthly_payment_due,
                         current_month=current_month,
                         member_group=member_group,
                         recent_group_contributions=recent_group_contributions,
                         member_rankings=member_rankings,
                         monthly_stats=monthly_stats)


@app.route('/member/profile', methods=['GET', 'POST'])
@login_required
def member_profile():
    """Member profile update page"""
    if current_user.is_admin:
        flash('Please use the admin portal for admin access', 'error')
        return redirect(url_for('admin_login'))
    
    if request.method == 'POST':
        # Update profile information
        current_user.full_name = request.form.get('full_name')
        current_user.email = request.form.get('email')
        current_user.phone_number = request.form.get('phone_number')
        
        # Update password if provided
        new_password = request.form.get('new_password')
        confirm_password = request.form.get('confirm_password')
        
        if new_password:
            if new_password != confirm_password:
                flash('New password and confirmation do not match', 'error')
                return render_template('member/profile.html')
            
            if len(new_password) < 6:
                flash('Password must be at least 6 characters long', 'error')
                return render_template('member/profile.html')
            
            # Verify current password
            current_password = request.form.get('current_password')
            if not current_password or not check_password_hash(current_user.password_hash, current_password):
                flash('Current password is incorrect', 'error')
                return render_template('member/profile.html')
            
            current_user.password_hash = generate_password_hash(new_password)
        
        try:
            db.session.commit()
            flash('Profile updated successfully', 'success')
            return redirect(url_for('member_dashboard'))
        except Exception as e:
            db.session.rollback()
            flash('Error updating profile. Please try again.', 'error')
    
    return render_template('member/profile.html')


@app.route('/member/contributions')
@login_required
def member_contributions():
    """Member contributions history page"""
    if current_user.is_admin:
        flash('Please use the admin portal for admin access', 'error')
        return redirect(url_for('admin_login'))
    
    member_group = current_user.group
    if not member_group:
        flash('You are not assigned to any group. Contact your administrator.', 'error')
        return redirect(url_for('member_login'))
    
    # Get all contributions with pagination
    page = request.args.get('page', 1, type=int)
    per_page = 20
    
    contributions = Contribution.query.filter_by(
        member_id=current_user.id,
        group_id=member_group.id
    ).order_by(Contribution.created_at.desc()).paginate(
        page=page, per_page=per_page, error_out=False
    )
    
    # Calculate totals by type
    monthly_total = db.session.query(db.func.sum(Contribution.amount)).filter_by(
        member_id=current_user.id,
        group_id=member_group.id,
        contribution_type='monthly'
    ).scalar() or 0
    
    special_total = db.session.query(db.func.sum(Contribution.amount)).filter_by(
        member_id=current_user.id,
        group_id=member_group.id,
        contribution_type='special'
    ).scalar() or 0
    
    penalty_total = db.session.query(db.func.sum(Contribution.amount)).filter_by(
        member_id=current_user.id,
        group_id=member_group.id,
        contribution_type='penalty'
    ).scalar() or 0
    
    bonus_total = db.session.query(db.func.sum(Contribution.amount)).filter_by(
        member_id=current_user.id,
        group_id=member_group.id,
        contribution_type='bonus'
    ).scalar() or 0
    
    return render_template('member/contributions.html',
                         contributions=contributions,
                         monthly_total=monthly_total,
                         special_total=special_total,
                         penalty_total=penalty_total,
                         bonus_total=bonus_total,
                         member_group=member_group)


@app.route('/member/events')
@login_required
def member_events():
    """Member events page"""
    if current_user.is_admin:
        flash('Please use the admin portal for admin access', 'error')
        return redirect(url_for('admin_login'))
    
    member_group = current_user.group
    if not member_group:
        flash('You are not assigned to any group. Contact your administrator.', 'error')
        return redirect(url_for('member_login'))
    
    # Get all events for the member's group
    events = BigCollectionEvent.query.filter_by(
        group_id=member_group.id
    ).order_by(BigCollectionEvent.start_date.desc()).all()
    
    # Get member's participation in events
    participations = {}
    for event in events:
        participation = BigCollectionParticipation.query.filter_by(
            event_id=event.id,
            member_id=current_user.id
        ).first()
        participations[event.id] = participation
    
    return render_template('member/events.html',
                         events=events,
                         participations=participations,
                         member_group=member_group)


@app.route('/member/submit-contribution', methods=['GET', 'POST'])
@login_required
def member_submit_contribution():
    """Member contribution submission page"""
    if current_user.is_admin:
        flash('Please use the admin portal for admin access', 'error')
        return redirect(url_for('admin_login'))
    
    member_group = current_user.group
    if not member_group:
        flash('You are not assigned to any group. Contact your administrator.', 'error')
        return redirect(url_for('member_login'))
    
    if request.method == 'POST':
        try:
            amount = Decimal(request.form.get('amount'))
            contribution_type = request.form.get('contribution_type')
            description = request.form.get('description', '')
            payment_method = request.form.get('payment_method')
            reference_number = request.form.get('reference_number', '')
            contribution_month = None
            
            # For monthly contributions, get the month
            if contribution_type == 'monthly':
                month_str = request.form.get('contribution_month')
                if month_str:
                    contribution_month = datetime.strptime(month_str, '%Y-%m').date().replace(day=1)
            
            # Create contribution request
            contribution_request = ContributionRequest(
                member_id=current_user.id,
                group_id=member_group.id,
                currency_id=member_group.currency_id,
                amount=amount,
                contribution_type=contribution_type,
                contribution_month=contribution_month,
                description=description,
                payment_method=payment_method,
                reference_number=reference_number
            )
            
            db.session.add(contribution_request)
            db.session.commit()
            
            flash('Contribution request submitted successfully! Your admin will review and approve it.', 'success')
            return redirect(url_for('member_submit_contribution'))
            
        except ValueError:
            flash('Please enter a valid amount', 'error')
        except Exception as e:
            db.session.rollback()
            flash('Error submitting contribution request. Please try again.', 'error')
    
    # Get pending requests for this member
    pending_requests = ContributionRequest.query.filter_by(
        member_id=current_user.id,
        group_id=member_group.id,
        status='pending'
    ).order_by(ContributionRequest.created_at.desc()).all()
    
    return render_template('member/submit_contribution.html', 
                         member_group=member_group,
                         pending_requests=pending_requests)


@app.route('/member/request-loan', methods=['GET', 'POST'])
@login_required
def member_request_loan():
    """Member loan request page"""
    if current_user.is_admin:
        flash('Please use the admin portal for admin access', 'error')
        return redirect(url_for('admin_login'))
    
    member_group = current_user.group
    if not member_group:
        flash('You are not assigned to any group. Contact your administrator.', 'error')
        return redirect(url_for('member_login'))
    
    if request.method == 'POST':
        try:
            amount_requested = Decimal(request.form.get('amount_requested'))
            purpose = request.form.get('purpose')
            repayment_months = int(request.form.get('repayment_months'))
            
            # Calculate monthly payment (simple calculation without interest for now)
            monthly_payment = amount_requested / repayment_months
            
            # Create loan request
            loan_request = LoanRequest(
                member_id=current_user.id,
                group_id=member_group.id,
                currency_id=member_group.currency_id,
                amount_requested=amount_requested,
                purpose=purpose,
                repayment_months=repayment_months,
                monthly_payment=monthly_payment
            )
            
            db.session.add(loan_request)
            db.session.commit()
            
            flash('Loan request submitted successfully! Your admin will review and respond.', 'success')
            return redirect(url_for('member_request_loan'))
            
        except ValueError:
            flash('Please enter valid numeric values', 'error')
        except Exception as e:
            db.session.rollback()
            flash('Error submitting loan request. Please try again.', 'error')
    
    # Get loan requests for this member
    loan_requests = LoanRequest.query.filter_by(
        member_id=current_user.id,
        group_id=member_group.id
    ).order_by(LoanRequest.created_at.desc()).all()
    
    return render_template('member/request_loan.html', 
                         member_group=member_group,
                         loan_requests=loan_requests)


@app.route('/member/requests')
@login_required
def member_requests():
    """Member requests status page"""
    if current_user.is_admin:
        flash('Please use the admin portal for admin access', 'error')
        return redirect(url_for('admin_login'))
    
    member_group = current_user.group
    if not member_group:
        flash('You are not assigned to any group. Contact your administrator.', 'error')
        return redirect(url_for('member_login'))
    
    # Get all contribution requests
    contribution_requests = ContributionRequest.query.filter_by(
        member_id=current_user.id,
        group_id=member_group.id
    ).order_by(ContributionRequest.created_at.desc()).all()
    
    # Get all loan requests
    loan_requests = LoanRequest.query.filter_by(
        member_id=current_user.id,
        group_id=member_group.id
    ).order_by(LoanRequest.created_at.desc()).all()
    
    return render_template('member/requests.html',
                         member_group=member_group,
                         contribution_requests=contribution_requests,
                         loan_requests=loan_requests)


@app.route('/logout')
@login_required
def logout():
    """Logout user"""
    logout_user()
    return redirect(url_for('index'))


# ==================== SUPERADMIN ROUTES ====================

@app.route('/superadmin/dashboard')
@login_required
def superadmin_dashboard():
    """Superadmin dashboard with system overview"""
    if not current_user.is_superadmin:
        flash('Access denied - Superadmin required', 'error')
        return redirect(url_for('index'))
    
    # System Statistics
    total_members = Member.query.filter_by(is_active=True).count()
    total_admins = Member.query.filter_by(is_admin=True, is_active=True).count()
    total_superadmins = Member.query.filter_by(is_superadmin=True, is_active=True).count()
    total_contributions = db.session.query(func.sum(Contribution.amount)).scalar() or 0
    total_contribution_count = Contribution.query.count()
    active_events = BigCollectionEvent.query.filter_by(is_active=True).count()
    
    # Recent activity
    recent_members = Member.query.order_by(Member.created_at.desc()).limit(5).all()
    recent_contributions = Contribution.query.order_by(Contribution.created_at.desc()).limit(10).all()
    
    # Monthly statistics
    current_month = date.today().replace(day=1)
    monthly_contributions = db.session.query(func.sum(Contribution.amount)).filter(
        Contribution.created_at >= current_month
    ).scalar() or 0
    
    # Database statistics
    currencies_count = Currency.query.count()
    events_count = BigCollectionEvent.query.count()
    
    return render_template('superadmin/dashboard.html',
                         total_members=total_members,
                         total_admins=total_admins,
                         total_superadmins=total_superadmins,
                         total_contributions=total_contributions,
                         total_contribution_count=total_contribution_count,
                         active_events=active_events,
                         recent_members=recent_members,
                         recent_contributions=recent_contributions,
                         monthly_contributions=monthly_contributions,
                         currencies_count=currencies_count,
                         events_count=events_count)


@app.route('/superadmin/users')
@login_required
def superadmin_users():
    """Superadmin user management"""
    if not current_user.is_superadmin:
        flash('Access denied - Superadmin required', 'error')
        return redirect(url_for('index'))
    
    users = Member.query.order_by(Member.created_at.desc()).all()
    groups = Group.query.filter_by(is_active=True).order_by(Group.name).all()
    return render_template('superadmin/users.html', users=users, groups=groups)


@app.route('/superadmin/users/promote/<int:user_id>')
@login_required
def superadmin_promote_user(user_id):
    """Promote user to admin"""
    if not current_user.is_superadmin:
        flash('Access denied - Superadmin required', 'error')
        return redirect(url_for('index'))
    
    user = Member.query.get_or_404(user_id)
    user.is_admin = True
    db.session.commit()
    flash(f'{user.full_name} has been promoted to admin', 'success')
    return redirect(url_for('superadmin_users'))


@app.route('/superadmin/users/demote/<int:user_id>')
@login_required
def superadmin_demote_user(user_id):
    """Demote admin to regular user"""
    if not current_user.is_superadmin:
        flash('Access denied - Superadmin required', 'error')
        return redirect(url_for('index'))
    
    user = Member.query.get_or_404(user_id)
    if user.is_superadmin:
        flash('Cannot demote superadmin users', 'error')
        return redirect(url_for('superadmin_users'))
    
    user.is_admin = False
    db.session.commit()
    flash(f'{user.full_name} has been demoted to regular user', 'success')
    return redirect(url_for('superadmin_users'))


@app.route('/superadmin/users/toggle-status/<int:user_id>')
@login_required
def superadmin_toggle_user_status(user_id):
    """Toggle user active status"""
    if not current_user.is_superadmin:
        flash('Access denied - Superadmin required', 'error')
        return redirect(url_for('index'))
    
    user = Member.query.get_or_404(user_id)
    if user.is_superadmin and user.id != current_user.id:
        flash('Cannot deactivate other superadmin users', 'error')
        return redirect(url_for('superadmin_users'))
    
    user.is_active = not user.is_active
    status = 'activated' if user.is_active else 'deactivated'
    db.session.commit()
    flash(f'{user.full_name} has been {status}', 'success')
    return redirect(url_for('superadmin_users'))


@app.route('/superadmin/users/assign-group/<int:user_id>', methods=['POST'])
@login_required
def superadmin_assign_group(user_id):
    """Assign user to a group"""
    if not current_user.is_superadmin:
        flash('Access denied - Superadmin required', 'error')
        return redirect(url_for('index'))
    
    user = Member.query.get_or_404(user_id)
    group_code = request.form.get('group_code')
    
    if group_code:
        group = Group.query.filter_by(group_code=group_code).first()
        if group:
            user.group_id = group.id
            flash(f'{user.full_name} has been assigned to {group.name}', 'success')
        else:
            flash('Selected group not found', 'error')
    else:
        # Remove from group
        old_group_name = user.group.name if user.group else None
        user.group_id = None
        if old_group_name:
            flash(f'{user.full_name} has been removed from {old_group_name}', 'success')
        else:
            flash(f'{user.full_name} group assignment cleared', 'info')
    
    db.session.commit()
    return redirect(url_for('superadmin_users'))


@app.route('/superadmin/system')
@login_required
def superadmin_system():
    """System management and settings"""
    if not current_user.is_superadmin:
        flash('Access denied - Superadmin required', 'error')
        return redirect(url_for('index'))
    
    # Database statistics
    db_stats = {
        'groups': Group.query.count(),
        'members': Member.query.count(),
        'contributions': Contribution.query.count(),
        'events': BigCollectionEvent.query.count(),
        'currencies': Currency.query.count(),
        'participations': BigCollectionParticipation.query.count()
    }
    
    return render_template('superadmin/system.html', db_stats=db_stats)


@app.route('/superadmin/analytics')
@login_required
def superadmin_analytics():
    """Advanced analytics and reporting"""
    if not current_user.is_superadmin:
        flash('Access denied - Superadmin required', 'error')
        return redirect(url_for('index'))
    
    # Monthly contribution trends
    monthly_data = db.session.query(
        func.date_trunc('month', Contribution.created_at).label('month'),
        func.sum(Contribution.amount).label('total_amount'),
        func.count(Contribution.id).label('contribution_count')
    ).group_by('month').order_by('month').limit(12).all()
    
    # Top contributors
    top_contributors = db.session.query(
        Member.full_name,
        Member.custom_id,
        func.sum(Contribution.amount).label('total_contributed'),
        func.count(Contribution.id).label('contribution_count')
    ).join(Contribution, Member.id == Contribution.member_id).group_by(Member.id, Member.full_name, Member.custom_id).order_by(
        func.sum(Contribution.amount).desc()
    ).limit(10).all()
    
    # Contribution type breakdown
    type_breakdown = db.session.query(
        Contribution.contribution_type,
        func.sum(Contribution.amount).label('total_amount'),
        func.count(Contribution.id).label('count')
    ).group_by(Contribution.contribution_type).all()
    
    return render_template('superadmin/analytics.html',
                         monthly_data=monthly_data,
                         top_contributors=top_contributors,
                         type_breakdown=type_breakdown)


# Admin Management Routes
@app.route('/admin/members')
@login_required
def admin_members():
    """Admin members management - group-specific"""
    if not current_user.is_admin:
        flash('Access denied', 'error')
        return redirect(url_for('index'))
    
    # Get group context
    group_id = None
    if current_user.is_superadmin:
        # Allow superadmin to manage all groups or specify group context
        group_code = request.args.get('group')
        if group_code:
            group = Group.query.filter_by(group_code=group_code).first()
            if group:
                group_id = group.id
    else:
        # Regular admin can only see their own group members
        group_id = current_user.group_id
    
    # Filter members by group
    if group_id:
        members = Member.query.filter_by(group_id=group_id, is_admin=False).order_by(Member.custom_id.asc()).all()
        group = Group.query.get(group_id)
    else:
        # For superadmin without group filter, show all members
        members = Member.query.filter_by(is_admin=False).order_by(Member.custom_id.asc()).all()
        group = None
    
    return render_template('admin/members.html', members=members, group=group)


@app.route('/admin/members/add', methods=['GET', 'POST'])
@login_required
def admin_add_member():
    """Add new member"""
    if not current_user.is_admin:
        flash('Access denied', 'error')
        return redirect(url_for('index'))
    
    if request.method == 'POST':
        custom_id = request.form.get('custom_id')
        username = request.form.get('username')
        email = request.form.get('email')
        full_name = request.form.get('full_name')
        phone_number = request.form.get('phone_number')
        password = request.form.get('password')
        is_admin = request.form.get('is_admin') == 'on'
        
        # Check if username, email, or custom_id already exists
        if Member.query.filter_by(username=username).first():
            flash('Username already exists', 'error')
        elif Member.query.filter_by(email=email).first():
            flash('Email already exists', 'error')
        elif custom_id and Member.query.filter_by(custom_id=custom_id).first():
            flash('Custom ID already exists', 'error')
        else:
            member = Member(
                custom_id=custom_id if custom_id else None,
                username=username,
                email=email,
                full_name=full_name,
                phone_number=phone_number,
                password_hash=generate_password_hash(password),
                is_admin=is_admin,
                group_id=current_user.group_id  # Assign member to admin's group
            )
            db.session.add(member)
            db.session.commit()
            flash('Member added successfully', 'success')
            return redirect(url_for('admin_members'))
    
    return render_template('admin/add_member.html')


@app.route('/admin/members/edit/<int:member_id>', methods=['GET', 'POST'])
@login_required
def admin_edit_member(member_id):
    """Edit member"""
    if not current_user.is_admin:
        flash('Access denied', 'error')
        return redirect(url_for('index'))
    
    member = Member.query.get_or_404(member_id)
    
    # Check if admin can edit this member (must be from same group unless superadmin)
    if not current_user.is_superadmin:
        if member.group_id != current_user.group_id:
            flash('You can only edit members from your group', 'error')
            return redirect(url_for('admin_members'))
    
    if request.method == 'POST':
        custom_id = request.form.get('custom_id')
        username = request.form.get('username')
        email = request.form.get('email')
        full_name = request.form.get('full_name')
        phone_number = request.form.get('phone_number')
        password = request.form.get('password')
        is_active = request.form.get('is_active') == 'on'
        
        # Check if username already exists (excluding current member)
        existing_username = Member.query.filter(Member.username == username, Member.id != member_id).first()
        if existing_username:
            flash('Username already exists', 'error')
            return render_template('admin/edit_member.html', member=member)
        
        # Check if email already exists (excluding current member)
        existing_email = Member.query.filter(Member.email == email, Member.id != member_id).first()
        if existing_email:
            flash('Email already exists', 'error')
            return render_template('admin/edit_member.html', member=member)
        
        # Check if custom_id already exists (excluding current member)
        if custom_id:
            existing_custom_id = Member.query.filter(Member.custom_id == custom_id, Member.id != member_id).first()
            if existing_custom_id:
                flash('Custom ID already exists', 'error')
                return render_template('admin/edit_member.html', member=member)
        
        # Update member
        member.custom_id = custom_id if custom_id else None
        member.username = username
        member.email = email
        member.full_name = full_name
        member.phone_number = phone_number
        member.is_active = is_active
        
        # Update password if provided
        if password:
            member.password_hash = generate_password_hash(password)
        
        member.updated_at = datetime.utcnow()
        db.session.commit()
        
        flash('Member updated successfully', 'success')
        return redirect(url_for('admin_members'))
    
    return render_template('admin/edit_member.html', member=member)


@app.route('/admin/members/delete/<int:member_id>', methods=['POST'])
@login_required
def admin_delete_member(member_id):
    """Delete member"""
    if not current_user.is_admin:
        flash('Access denied', 'error')
        return redirect(url_for('index'))
    
    member = Member.query.get_or_404(member_id)
    
    # Check if admin can delete this member (must be from same group unless superadmin)
    if not current_user.is_superadmin:
        if member.group_id != current_user.group_id:
            flash('You can only delete members from your group', 'error')
            return redirect(url_for('admin_members'))
    
    # Prevent deleting admin users
    if member.is_admin:
        flash('Cannot delete admin users', 'error')
        return redirect(url_for('admin_members'))
    
    # Check if member has contributions
    contributions_count = Contribution.query.filter_by(member_id=member_id).count()
    if contributions_count > 0:
        flash(f'Cannot delete member with {contributions_count} contributions. Deactivate instead.', 'error')
        return redirect(url_for('admin_members'))
    
    db.session.delete(member)
    db.session.commit()
    
    flash('Member deleted successfully', 'success')
    return redirect(url_for('admin_members'))


@app.route('/admin/contributions')
@login_required
def admin_contributions():
    """Admin contributions management"""
    if not current_user.is_admin:
        flash('Access denied', 'error')
        return redirect(url_for('index'))
    
    # Get search parameters
    search = request.args.get('search', '')
    month_filter = request.args.get('month_filter', '')
    type_filter = request.args.get('type_filter', '')
    page = request.args.get('page', 1, type=int)
    per_page = 20  # Show 20 contributions per page
    
    # Build query - specify the join condition explicitly and filter by group
    query = Contribution.query.join(Member, Contribution.member_id == Member.id)
    
    # Filter by current user's group if not superadmin
    if not current_user.is_superadmin:
        if current_user.group_id:
            query = query.filter(Member.group_id == current_user.group_id)
        else:
            # If admin has no group, show no contributions
            query = query.filter(Member.group_id == -1)
    
    # Apply search filter
    if search:
        query = query.filter(
            db.or_(
                Member.username.ilike(f'%{search}%'),
                Member.full_name.ilike(f'%{search}%'),
                Member.custom_id.ilike(f'%{search}%'),
                Contribution.description.ilike(f'%{search}%')
            )
        )
    
    # Apply month filter
    if month_filter:
        year, month = month_filter.split('-')
        query = query.filter(
            db.extract('year', Contribution.contribution_month) == int(year),
            db.extract('month', Contribution.contribution_month) == int(month)
        )
    
    # Apply type filter
    if type_filter:
        query = query.filter(Contribution.contribution_type == type_filter)
    
    # Apply pagination
    contributions_paginated = query.order_by(Contribution.created_at.desc()).paginate(
        page=page, 
        per_page=per_page, 
        error_out=False
    )
    
    # Get available months for filter dropdown - also filter by group
    available_months_query = db.session.query(Contribution.contribution_month).join(Member, Contribution.member_id == Member.id).distinct()
    if not current_user.is_superadmin:
        if current_user.group_id:
            available_months_query = available_months_query.filter(Member.group_id == current_user.group_id)
        else:
            available_months_query = available_months_query.filter(Member.group_id == -1)
    
    available_months = available_months_query.filter(
        Contribution.contribution_month.isnot(None)
    ).order_by(Contribution.contribution_month.desc()).all()
    available_months = [month[0] for month in available_months if month[0]]
    
    return render_template('admin/contributions.html', 
                         contributions=contributions_paginated.items,
                         pagination=contributions_paginated,
                         available_months=available_months)


@app.route('/admin/contributions/add', methods=['GET', 'POST'])
@login_required
def admin_add_contribution():
    """Add new contribution"""
    if not current_user.is_admin:
        flash('Access denied', 'error')
        return redirect(url_for('index'))
    
    if request.method == 'POST':
        member_id = request.form.get('member_id')
        amount = request.form.get('amount')
        contribution_type = request.form.get('contribution_type')
        description = request.form.get('description')
        contribution_month = request.form.get('contribution_month')
        contribution_date = request.form.get('contribution_date')
        
        # Check if trying to add contribution for admin
        member = Member.query.get(member_id)
        if member and member.is_admin:
            flash('Cannot add contributions for admin users', 'error')
            return redirect(url_for('admin_add_contribution'))
        
        # Check if member belongs to current user's group (unless superadmin)
        if not current_user.is_superadmin:
            if not member or member.group_id != current_user.group_id:
                flash('You can only add contributions for members in your group', 'error')
                return redirect(url_for('admin_add_contribution'))
        
        currency = Currency.query.filter_by(is_default=True).first()
        if not currency:
            flash('No default currency set', 'error')
            return redirect(url_for('admin_add_contribution'))
        
        # Set the created_at date if provided (for historical data)
        created_at = datetime.utcnow()
        if contribution_date:
            created_at = datetime.strptime(contribution_date, '%Y-%m-%d')
        
        contribution = Contribution(
            member_id=member_id,
            group_id=current_user.group_id,  # Set group_id for proper isolation
            currency_id=currency.id,
            amount=float(amount),
            contribution_type=contribution_type,
            description=description,
            contribution_month=datetime.strptime(contribution_month, '%Y-%m-%d').date() if contribution_month else None,
            recorded_by=current_user.id,
            created_at=created_at
        )
        db.session.add(contribution)
        db.session.commit()
        flash('Contribution added successfully', 'success')
        return redirect(url_for('admin_contributions'))
    
    # Order members by custom_id for contribution forms, exclude admin users and filter by group
    members_query = Member.query.filter_by(is_active=True, is_admin=False)
    if not current_user.is_superadmin:
        if current_user.group_id:
            members_query = members_query.filter_by(group_id=current_user.group_id)
        else:
            members_query = members_query.filter_by(group_id=-1)  # Show no members if admin has no group
    
    members = members_query.order_by(Member.custom_id.asc()).all()
    return render_template('admin/add_contribution.html', members=members)


@app.route('/admin/events')
@login_required
def admin_events():
    """Admin events management"""
    if not current_user.is_admin:
        flash('Access denied', 'error')
        return redirect(url_for('index'))
    
    # Filter events by group
    events_query = BigCollectionEvent.query
    if not current_user.is_superadmin:
        if current_user.group_id:
            events_query = events_query.filter_by(group_id=current_user.group_id)
        else:
            events_query = events_query.filter_by(group_id=-1)  # Show no events if admin has no group
    
    events = events_query.order_by(BigCollectionEvent.created_at.desc()).all()
    return render_template('admin/events.html', events=events)


@app.route('/admin/events/create', methods=['GET', 'POST'])
@login_required
def admin_create_event():
    """Create new big collection event"""
    if not current_user.is_admin:
        flash('Access denied', 'error')
        return redirect(url_for('index'))
    
    if request.method == 'POST':
        name = request.form.get('name')
        description = request.form.get('description')
        target_amount = request.form.get('target_amount')
        start_date = request.form.get('start_date')
        end_date = request.form.get('end_date')
        
        currency = Currency.query.filter_by(is_default=True).first()
        if not currency:
            flash('No default currency set', 'error')
            return redirect(url_for('admin_create_event'))
        
        event = BigCollectionEvent(
            name=name,
            description=description,
            target_amount=float(target_amount) if target_amount else None,
            currency_id=currency.id,
            group_id=current_user.group_id,  # Set group_id for proper isolation
            start_date=datetime.strptime(start_date, '%Y-%m-%d').date(),
            end_date=datetime.strptime(end_date, '%Y-%m-%d').date(),
            created_by=current_user.id
        )
        db.session.add(event)
        db.session.commit()
        flash('Event created successfully', 'success')
        return redirect(url_for('admin_events'))
    
    return render_template('admin/create_event.html')


@app.route('/admin/bulk-import', methods=['GET', 'POST'])
@login_required
def admin_bulk_import():
    """Bulk import historical contributions"""
    if not current_user.is_admin:
        flash('Access denied', 'error')
        return redirect(url_for('index'))
    
    if request.method == 'POST':
        member_ids = request.form.getlist('member_ids')
        contribution_type = request.form.get('contribution_type')
        description = request.form.get('description')
        import_mode = request.form.get('import_mode', 'simple')
        
        if not member_ids:
            flash('Please select at least one member', 'error')
            return redirect(url_for('admin_bulk_import'))
        
        currency = Currency.query.filter_by(is_default=True).first()
        if not currency:
            flash('No default currency set', 'error')
            return redirect(url_for('admin_bulk_import'))
        
        added_count = 0
        skipped_count = 0
        processed_months = []
        
        if import_mode == 'simple':
            # Simple mode - same amount for all months
            start_month = request.form.get('start_month')
            end_month = request.form.get('end_month')
            monthly_amount = request.form.get('monthly_amount')
            
            if not start_month or not end_month or not monthly_amount:
                flash('Please select start month, end month, and amount', 'error')
                return redirect(url_for('admin_bulk_import'))
            
            # Generate list of months between start and end
            start_date = datetime.strptime(start_month, '%Y-%m-%d').date()
            end_date = datetime.strptime(end_month, '%Y-%m-%d').date()
            
            if start_date > end_date:
                flash('Start month must be before or same as end month', 'error')
                return redirect(url_for('admin_bulk_import'))
            
            # Generate all months between start and end
            selected_months = []
            current_date = start_date
            while current_date <= end_date:
                selected_months.append({
                    'date': current_date.strftime('%Y-%m-%d'),
                    'amount': float(monthly_amount)
                })
                # Move to next month
                if current_date.month == 12:
                    current_date = current_date.replace(year=current_date.year + 1, month=1)
                else:
                    current_date = current_date.replace(month=current_date.month + 1)
        
        else:
            # Advanced mode - different amounts for different months
            selected_months = []
            for year in range(2024, 2026):
                for month in range(1, 13):
                    month_key = f"{year}_{month}"
                    month_date = f"{year:04d}-{month:02d}-01"
                    amount_key = f"amount_{month_key}"
                    
                    # Check if month is selected
                    if month_date in request.form.getlist('months'):
                        amount = request.form.get(amount_key)
                        if amount and float(amount) > 0:
                            selected_months.append({
                                'date': month_date,
                                'amount': float(amount)
                            })
            
            if not selected_months:
                flash('Please select at least one month with a valid amount', 'error')
                return redirect(url_for('admin_bulk_import'))
        
        # Process contributions for each selected member and month
        for member_id in member_ids:
            # Check if member is admin and skip
            member = Member.query.get(member_id)
            if member and member.is_admin:
                continue
            
            # Check if member belongs to current user's group (unless superadmin)
            if not current_user.is_superadmin:
                if not member or member.group_id != current_user.group_id:
                    continue
                
            for month_data in selected_months:
                month_date = datetime.strptime(month_data['date'], '%Y-%m-%d').date()
                amount = month_data['amount']
                
                if month_data['date'] not in processed_months:
                    processed_months.append(month_data['date'])
                
                # Check if contribution already exists for this member and month
                # For monthly contributions, skip if already exists
                # For special contributions, allow multiple entries
                if contribution_type == 'monthly':
                    existing = Contribution.query.filter_by(
                        member_id=member_id,
                        contribution_month=month_date,
                        contribution_type='monthly'
                    ).first()
                    
                    if existing:
                        skipped_count += 1
                        continue  # Skip if monthly contribution already exists
                # For special/penalty/bonus contributions, always allow adding
                
                contribution = Contribution(
                    member_id=member_id,
                    group_id=current_user.group_id,  # Set group_id for proper isolation
                    currency_id=currency.id,
                    amount=amount,
                    contribution_type=contribution_type,
                    description=description,
                    contribution_month=month_date,
                    recorded_by=current_user.id,
                    created_at=datetime(month_date.year, month_date.month, 15)  # Set to mid-month for historical data
                )
                db.session.add(contribution)
                added_count += 1
        
        db.session.commit()
        
        # Create detailed success message
        message = f'Successfully imported {added_count} contributions'
        if skipped_count > 0:
            message += f' ({skipped_count} skipped - already exist)'
        
        # Add details about processed months
        if processed_months:
            message += f' | Processed {len(processed_months)} months'
            
        flash(message, 'success')
        return redirect(url_for('admin_contributions'))
    
    # Order members by custom_id for bulk import and filter by group
    members_query = Member.query.filter_by(is_active=True)
    if not current_user.is_superadmin:
        if current_user.group_id:
            members_query = members_query.filter_by(group_id=current_user.group_id)
        else:
            members_query = members_query.filter_by(group_id=-1)  # Show no members if admin has no group
    
    members = members_query.order_by(Member.custom_id.asc()).all()
    return render_template('admin/bulk_import_enhanced.html', members=members)


@app.route('/admin/analytics')
@login_required
def admin_analytics_dashboard():
    """Advanced analytics dashboard for admins"""
    if not current_user.is_admin:
        flash('Access denied. Admin privileges required.', 'error')
        return redirect(url_for('index'))
    
    # Get current group
    if current_user.is_group_admin:
        group = current_user.group
    else:
        flash('Group assignment required.', 'error')
        return redirect(url_for('index'))
    
    # Advanced Analytics Data
    
    # 1. Monthly Contribution Trends (Last 12 months)
    monthly_trends = db.session.query(
        func.date_trunc('month', Contribution.created_at).label('month'),
        func.sum(Contribution.amount).label('total'),
        func.count(Contribution.id).label('count'),
        func.avg(Contribution.amount).label('avg_amount')
    ).filter(
        Contribution.group_id == group.id,
        Contribution.created_at >= datetime.now() - timedelta(days=365)
    ).group_by(func.date_trunc('month', Contribution.created_at)).order_by('month').all()
    
    # 2. Member Performance Analysis - Use subquery to avoid ambiguous joins
    member_performance = []
    try:
        members = Member.query.filter_by(group_id=group.id, is_admin=False).all()
        for member in members:
            total_contrib = db.session.query(func.sum(Contribution.amount))\
                .filter_by(member_id=member.id).scalar() or 0
            count_contrib = db.session.query(func.count(Contribution.id))\
                .filter_by(member_id=member.id).scalar() or 0
            avg_contrib = total_contrib / count_contrib if count_contrib > 0 else 0
            last_contrib = db.session.query(func.max(Contribution.created_at))\
                .filter_by(member_id=member.id).scalar()
            
            member_performance.append({
                'id': member.id,
                'full_name': member.full_name,
                'custom_id': member.custom_id,
                'total_contributions': float(total_contrib),
                'contribution_count': count_contrib,
                'avg_contribution': float(avg_contrib),
                'last_contribution': last_contrib
            })
        
        # Sort by total contributions
        member_performance.sort(key=lambda x: x['total_contributions'], reverse=True)
    except Exception as e:
        print(f"Error in member performance: {e}")
        member_performance = []
    
    # 3. Contribution Type Breakdown
    contribution_types = db.session.query(
        Contribution.contribution_type,
        func.sum(Contribution.amount).label('total'),
        func.count(Contribution.id).label('count')
    ).filter(Contribution.group_id == group.id)\
     .group_by(Contribution.contribution_type).all()
    
    # 4. Goal Tracking
    monthly_goal = 100000  # ৳1,00,000 monthly target
    current_month_total = db.session.query(func.sum(Contribution.amount))\
        .filter(
            Contribution.group_id == group.id,
            func.date_trunc('month', Contribution.created_at) == func.date_trunc('month', func.current_date())
        ).scalar() or 0
    
    goal_progress = (current_month_total / monthly_goal) * 100 if monthly_goal > 0 else 0
    
    # 5. Top Contributors (This month) - Simplified query
    try:
        current_month_start = datetime.now().replace(day=1, hour=0, minute=0, second=0, microsecond=0)
        top_contributors = []
        members = Member.query.filter_by(group_id=group.id, is_admin=False).all()
        
        for member in members:
            monthly_total = db.session.query(func.sum(Contribution.amount))\
                .filter(
                    Contribution.member_id == member.id,
                    Contribution.created_at >= current_month_start
                ).scalar() or 0
            
            if monthly_total > 0:
                top_contributors.append({
                    'full_name': member.full_name,
                    'custom_id': member.custom_id,
                    'monthly_total': float(monthly_total)
                })
        
        # Sort and limit to top 5
        top_contributors.sort(key=lambda x: x['monthly_total'], reverse=True)
        top_contributors = top_contributors[:5]
    except Exception as e:
        print(f"Error in top contributors: {e}")
        top_contributors = []
    
    # 6. Recent Activity (Last 30 days)
    recent_activity = db.session.query(Contribution)\
        .filter(
            Contribution.group_id == group.id,
            Contribution.created_at >= datetime.now() - timedelta(days=30)
        ).order_by(desc(Contribution.created_at)).limit(20).all()
    
    analytics_data = {
        'monthly_trends': monthly_trends,
        'member_performance': member_performance,
        'contribution_types': contribution_types,
        'recent_activity': recent_activity,
        'goal_progress': goal_progress,
        'current_month_total': current_month_total,
        'monthly_goal': monthly_goal,
        'top_contributors': top_contributors
    }
    
    return render_template('admin/analytics_dashboard.html', 
                         analytics=analytics_data, 
                         group=group)

@app.route('/admin/notifications')
@login_required
def admin_notification_center():
    """Advanced notification and alert system"""
    if not current_user.is_admin:
        flash('Access denied. Admin privileges required.', 'error')
        return redirect(url_for('index'))
    
    group = current_user.group
    notifications = []
    
    # 1. Overdue payments (members who haven't paid this month)
    current_month = datetime.now().replace(day=1, hour=0, minute=0, second=0, microsecond=0)
    
    # Get members who paid this month - simplified approach
    try:
        paid_member_ids = db.session.query(Contribution.member_id.distinct())\
            .filter(
                Contribution.group_id == group.id,
                Contribution.created_at >= current_month,
                Contribution.contribution_type == 'monthly'
            ).all()
        
        paid_member_ids = [row[0] for row in paid_member_ids]
        
        overdue_members = Member.query.filter(
            Member.group_id == group.id,
            Member.is_admin == False,
            ~Member.id.in_(paid_member_ids)
        ).all()
    except Exception as e:
        print(f"Error in overdue members: {e}")
        overdue_members = []
    
    if overdue_members:
        notifications.append({
            'type': 'warning',
            'title': f'{len(overdue_members)} Overdue Payments',
            'message': f'{len(overdue_members)} members have not paid this month',
            'action_url': url_for('admin_members'),
            'action_text': 'View Members',
            'priority': 'high',
            'count': len(overdue_members)
        })
    
    # 2. Pending loan requests
    pending_loans = LoanRequest.query.filter_by(group_id=group.id, status='pending').count()
    if pending_loans > 0:
        notifications.append({
            'type': 'info',
            'title': f'{pending_loans} Pending Loan Requests',
            'message': f'{pending_loans} loan requests require your review',
            'action_url': url_for('admin_dashboard'),
            'action_text': 'Review Requests',
            'priority': 'medium',
            'count': pending_loans
        })
    
    # 3. Monthly goal progress
    monthly_goal = 100000
    current_month_total = db.session.query(func.sum(Contribution.amount))\
        .filter(
            Contribution.group_id == group.id,
            func.date_trunc('month', Contribution.created_at) == func.date_trunc('month', func.current_date())
        ).scalar() or 0
    
    goal_progress = (current_month_total / monthly_goal) * 100
    
    if goal_progress < 50:
        notifications.append({
            'type': 'warning',
            'title': 'Monthly Goal Behind Schedule',
            'message': f'Only {goal_progress:.1f}% of monthly goal achieved',
            'action_url': url_for('admin_analytics_dashboard'),
            'action_text': 'View Analytics',
            'priority': 'medium',
            'count': int(goal_progress)
        })
    elif goal_progress >= 100:
        notifications.append({
            'type': 'success',
            'title': 'Monthly Goal Achieved!',
            'message': f'Congratulations! {goal_progress:.1f}% of monthly goal reached',
            'action_url': url_for('admin_analytics_dashboard'),
            'action_text': 'View Details',
            'priority': 'low',
            'count': int(goal_progress)
        })
    
    # Sort notifications by priority
    priority_order = {'high': 3, 'medium': 2, 'low': 1}
    notifications.sort(key=lambda x: priority_order.get(x['priority'], 0), reverse=True)
    
    return render_template('admin/notification_center.html', 
                         notifications=notifications, 
                         group=group)

@app.route('/admin/reports')
@login_required
def admin_reports():
    """Admin reports"""
    if not current_user.is_admin:
        flash('Access denied', 'error')
        return redirect(url_for('index'))
    
    # Get summary statistics filtered by group
    members_query = Member.query.filter_by(is_active=True)
    contributions_query = db.session.query(db.func.sum(Contribution.amount))
    
    if not current_user.is_superadmin:
        if current_user.group_id:
            members_query = members_query.filter_by(group_id=current_user.group_id)
            contributions_query = contributions_query.join(Member, Contribution.member_id == Member.id).filter(Member.group_id == current_user.group_id)
        else:
            members_query = members_query.filter_by(group_id=-1)
            contributions_query = contributions_query.join(Member, Contribution.member_id == Member.id).filter(Member.group_id == -1)
    
    total_members = members_query.count()
    total_contributions = contributions_query.scalar() or 0
    
    monthly_contributions_query = db.session.query(
        db.func.date_trunc('month', Contribution.created_at).label('month'),
        db.func.sum(Contribution.amount).label('total')
    )
    
    if not current_user.is_superadmin:
        if current_user.group_id:
            monthly_contributions_query = monthly_contributions_query.join(Member, Contribution.member_id == Member.id).filter(Member.group_id == current_user.group_id)
        else:
            monthly_contributions_query = monthly_contributions_query.join(Member, Contribution.member_id == Member.id).filter(Member.group_id == -1)
    
    monthly_contributions = monthly_contributions_query.group_by('month').order_by('month').all()
    
    return render_template('admin/reports.html', 
                         total_members=total_members,
                         total_contributions=total_contributions,
                         monthly_contributions=monthly_contributions)


@app.route('/admin/member-balances')
@login_required
def admin_member_balances():
    """Admin member balances - shows total contributions per member"""
    if not current_user.is_admin:
        flash('Access denied', 'error')
        return redirect(url_for('index'))
    
    # Get all active members with their total contributions filtered by group
    member_balances_query = db.session.query(
        Member.id,
        Member.username,
        Member.full_name,
        Member.custom_id,
        Member.email,
        db.func.coalesce(db.func.sum(Contribution.amount), 0).label('total_contributions'),
        db.func.count(Contribution.id).label('contribution_count')
    ).outerjoin(Contribution, Member.id == Contribution.member_id)\
     .filter(Member.is_active == True, Member.is_admin == False)
    
    if not current_user.is_superadmin:
        if current_user.group_id:
            member_balances_query = member_balances_query.filter(Member.group_id == current_user.group_id)
        else:
            member_balances_query = member_balances_query.filter(Member.group_id == -1)
    
    member_balances_raw = member_balances_query.group_by(Member.id, Member.username, Member.full_name, Member.custom_id, Member.email)\
                                              .order_by(Member.custom_id).all()
    
    # Convert to list of dictionaries with float values
    member_balances = []
    for member in member_balances_raw:
        member_dict = {
            'id': member.id,
            'username': member.username,
            'full_name': member.full_name,
            'custom_id': member.custom_id,
            'email': member.email,
            'total_contributions': float(member.total_contributions),
            'contribution_count': member.contribution_count
        }
        member_balances.append(member_dict)
    
    # Get overall statistics filtered by group
    total_amount_query = db.session.query(db.func.sum(Contribution.amount))
    if not current_user.is_superadmin:
        if current_user.group_id:
            total_amount_query = total_amount_query.join(Member, Contribution.member_id == Member.id).filter(Member.group_id == current_user.group_id)
        else:
            total_amount_query = total_amount_query.join(Member, Contribution.member_id == Member.id).filter(Member.group_id == -1)
    
    total_amount = float(total_amount_query.scalar() or 0)
    total_members = len(member_balances)
    average_contribution = total_amount / total_members if total_members > 0 else 0.0
    
    # Get currency symbol
    currency = Currency.query.filter_by(is_default=True).first()
    currency_symbol = currency.symbol if currency else '৳'
    
    return render_template('admin/member_balances.html',
                         member_balances=member_balances,
                         total_amount=total_amount,
                         total_members=total_members,
                         average_contribution=average_contribution,
                         currency_symbol=currency_symbol)


# Initialize default data
@app.route('/init-data')
def init_data():
    """Initialize default data (for development)"""
    if os.environ.get('FLASK_ENV') != 'development':
        return "Not allowed in production", 403
    
    # Create default currency
    if not Currency.query.first():
        bdt = Currency(name='Bangladeshi Taka', code='BDT', symbol='৳', is_default=True)
        db.session.add(bdt)
    
    # Create admin user
    if not Member.query.filter_by(is_admin=True).first():
        admin = Member(
            username='admin',
            email='admin@example.com',
            password_hash=generate_password_hash('admin123'),
            full_name='System Administrator',
            is_admin=True,
            is_superadmin=True,
            custom_id='A001'
        )
        db.session.add(admin)
    
    # Create default group
    if not Group.query.first():
        default_currency = Currency.query.filter_by(is_default=True).first()
        if default_currency:
            admin_user = Member.query.filter_by(is_admin=True).first()
            if admin_user:
                default_group = Group(
                    name='Default Group',
                    description='Default savings group',
                    group_code='GRP001',
                    currency_id=default_currency.id,
                    created_by=admin_user.id
                )
                db.session.add(default_group)
    
    # Create sample member
    if not Member.query.filter_by(username='john').first():
        default_group = Group.query.filter_by(group_code='GRP001').first()
        member = Member(
            username='john',
            email='john@example.com',
            password_hash=generate_password_hash('john123'),
            full_name='John Doe',
            phone_number='+8801234567890',
            group_id=default_group.id if default_group else None,
            custom_id='M001'
        )
        db.session.add(member)
    
    db.session.commit()
    return "Default data initialized successfully!"


@app.route('/bulk-add-members')
def bulk_add_members():
    """Bulk add members with default password 1234"""
    
    members_data = [
        "main uddin",
        "habib bashar", 
        "md sumon prodhan",
        "fahim ashab sohan",
        "faysal mahmud sezan",
        "fozle rabby",
        "Mowsumi akter",
        "neamot",
        "jahangir alom",
        "sahed rana",
        "jafor Hossain",
        "mehedi hasan",
        "enamul ismam",
        "mohammad",
        "rakib sobuj",
        "rakib habib",
        "saiful",
        "lutfor nahar"
    ]
    
    added_count = 0
    skipped_count = 0
    
    for full_name in members_data:
        # Create username from full name (remove spaces, lowercase)
        username = full_name.replace(" ", "").lower()
        
        # Check if member already exists
        if Member.query.filter_by(username=username).first():
            skipped_count += 1
            continue
            
        # Create email from username
        email = f"{username}@example.com"
        
        member = Member(
            username=username,
            email=email,
            password_hash=generate_password_hash('1234'),
            full_name=full_name.title(),
            is_admin=False,
            is_active=True
        )
        
        db.session.add(member)
        added_count += 1
    
    db.session.commit()
    
    return f"Bulk member creation completed! Added: {added_count}, Skipped (already exists): {skipped_count}"


@app.route('/assign-custom-ids')
def assign_custom_ids():
    """Assign custom IDs to all members who don't have one"""
    
    # Get all members without custom IDs
    members_without_ids = Member.query.filter(Member.custom_id.is_(None)).all()
    
    assigned_count = 0
    
    for member in members_without_ids:
        # Generate custom ID based on sequence (M001, M002, etc.)
        existing_count = Member.query.filter(Member.custom_id.isnot(None)).count()
        next_number = existing_count + assigned_count + 1
        custom_id = f"M{next_number:03d}"  # Format as M001, M002, etc.
        
        # Check if this ID already exists (safety check)
        while Member.query.filter_by(custom_id=custom_id).first():
            next_number += 1
            custom_id = f"M{next_number:03d}"
        
        member.custom_id = custom_id
        assigned_count += 1
    
    db.session.commit()
    
    return f"Custom IDs assigned! {assigned_count} members now have custom IDs."


# Group Management Routes
@app.route('/superadmin/groups')
@login_required
def superadmin_groups():
    """Group management for superadmin"""
    if not current_user.is_superadmin:
        flash('Access denied - Superadmin required', 'error')
        return redirect(url_for('index'))
    
    groups = Group.query.order_by(Group.name).all()
    total_members = Member.query.filter_by(is_active=True, is_admin=False).count()
    total_contributions = db.session.query(func.sum(Contribution.amount)).scalar() or 0
    
    return render_template('superadmin/groups_new.html', 
                         groups=groups,
                         total_members=total_members,
                         total_contributions=total_contributions)


@app.route('/superadmin/groups/add', methods=['GET', 'POST'])
@login_required
def superadmin_add_group():
    """Add new group with optional admin assignment"""
    if not current_user.is_superadmin:
        flash('Access denied - Superadmin required', 'error')
        return redirect(url_for('index'))
    
    if request.method == 'POST':
        name = request.form.get('name')
        description = request.form.get('description')
        group_code = request.form.get('group_code')
        currency_id = request.form.get('currency_id')
        admin_username = request.form.get('admin_username')
        admin_password = request.form.get('admin_password')
        admin_full_name = request.form.get('admin_full_name')
        admin_email = request.form.get('admin_email')
        
        # Check if group code already exists
        if Group.query.filter_by(group_code=group_code).first():
            flash('Group code already exists', 'error')
        else:
            # Create the group first
            group = Group(
                name=name,
                description=description,
                group_code=group_code,
                currency_id=currency_id,
                created_by=current_user.id
            )
            db.session.add(group)
            db.session.flush()  # Get the group ID
            
            # Create group admin if details provided
            if admin_username and admin_password and admin_full_name and admin_email:
                # Check if admin username already exists
                if Member.query.filter_by(username=admin_username).first():
                    flash('Admin username already exists', 'error')
                    db.session.rollback()
                    currencies = Currency.query.all()
                    return render_template('superadmin/add_group.html', currencies=currencies)
                
                # Generate admin custom ID
                admin_custom_id = f'A{group.id:03d}'
                
                group_admin = Member(
                    username=admin_username,
                    email=admin_email,
                    password_hash=generate_password_hash(admin_password),
                    full_name=admin_full_name,
                    custom_id=admin_custom_id,
                    group_id=group.id,
                    is_admin=True,
                    is_group_admin=True,
                    is_active=True
                )
                db.session.add(group_admin)
            
            db.session.commit()
            success_msg = f'Group "{name}" created successfully'
            if admin_username:
                success_msg += f' with admin "{admin_username}"'
            flash(success_msg, 'success')
            return redirect(url_for('superadmin_groups'))
    
    currencies = Currency.query.all()
    return render_template('superadmin/add_group.html', currencies=currencies)


@app.route('/group-select')
@login_required 
def group_select():
    """Group selection for admins"""
    if not current_user.is_admin and not current_user.is_superadmin:
        flash('Access denied', 'error')
        return redirect(url_for('index'))
    
    if current_user.is_superadmin:
        groups = Group.query.all()
    else:
        groups = [current_user.group] if current_user.group else []
    
    return render_template('admin/group_select.html', groups=groups)


@app.route('/group/<group_code>/admin')
@login_required
def group_admin_dashboard(group_code):
    """Group-specific admin dashboard"""
    group = Group.query.filter_by(group_code=group_code).first_or_404()
    
    # Check permissions
    if not current_user.is_superadmin and (not current_user.group_id or current_user.group_id != group.id):
        flash('Access denied to this group', 'error')
        return redirect(url_for('index'))
    
    # Group statistics
    total_members = Member.query.filter_by(group_id=group.id, is_admin=False).count()
    total_contributions = db.session.query(func.sum(Contribution.amount)).filter_by(group_id=group.id).scalar() or 0
    monthly_target = 5000
    
    # Get current month contributions
    current_month = datetime.now().replace(day=1).date()
    monthly_contributions = db.session.query(func.sum(Contribution.amount)).filter(
        Contribution.group_id == group.id,
        Contribution.created_at >= current_month
    ).scalar() or 0
    
    # Recent group activity
    recent_contributions = Contribution.query.filter_by(group_id=group.id).join(
        Member, Member.id == Contribution.member_id
    ).order_by(Contribution.created_at.desc()).limit(5).all()
    
    group_members = Member.query.filter_by(group_id=group.id, is_admin=False).order_by(Member.custom_id.asc()).all()
    
    return render_template('admin/group_dashboard.html',
                         group=group,
                         total_members=total_members,
                         total_contributions=total_contributions,
                         monthly_contributions=monthly_contributions,
                         monthly_target=monthly_target,
                         recent_contributions=recent_contributions,
                         group_members=group_members)


@app.route('/superadmin/groups/edit/<int:group_id>', methods=['POST'])
@login_required
def superadmin_edit_group(group_id):
    """Edit group details"""
    if not current_user.is_superadmin:
        flash('Access denied - Superadmin required', 'error')
        return redirect(url_for('index'))
    
    group = Group.query.get_or_404(group_id)
    group.name = request.form.get('group_name')
    group.description = request.form.get('description')
    group.is_active = bool(request.form.get('is_active'))
    
    db.session.commit()
    flash(f'Group "{group.name}" has been updated successfully', 'success')
    return redirect(url_for('superadmin_groups'))


@app.route('/superadmin/groups/toggle-status/<int:group_id>')
@login_required
def superadmin_toggle_group_status(group_id):
    """Toggle group active status"""
    if not current_user.is_superadmin:
        flash('Access denied - Superadmin required', 'error')
        return redirect(url_for('index'))
    
    group = Group.query.get_or_404(group_id)
    group.is_active = not group.is_active
    status = 'activated' if group.is_active else 'deactivated'
    
    db.session.commit()
    flash(f'Group "{group.name}" has been {status}', 'success')
    return redirect(url_for('superadmin_groups'))


@app.route('/signup', methods=['GET', 'POST'])
def group_signup():
    """Organization/Group signup for new users"""
    if request.method == 'POST':
        # Organization details
        organization_name = request.form.get('organization_name')
        organization_type = request.form.get('organization_type', 'savings_group')
        description = request.form.get('description')
        
        # Admin details
        admin_full_name = request.form.get('admin_full_name')
        admin_email = request.form.get('admin_email')
        admin_username = request.form.get('admin_username')
        admin_phone = request.form.get('admin_phone')
        admin_password = request.form.get('admin_password')
        agree_terms = request.form.get('agree_terms')
        
        # Validation
        if not all([organization_name, admin_full_name, admin_email, admin_username, admin_password]):
            flash('Please fill in all required fields', 'error')
            return render_template('public/group_signup.html')
        
        if not agree_terms:
            flash('Please agree to the Terms of Service and Privacy Policy', 'error')
            return render_template('public/group_signup.html')
        
        if len(admin_password) < 8:
            flash('Password must be at least 8 characters long', 'error')
            return render_template('public/group_signup.html')
        
        # Check if username or email already exists
        if Member.query.filter_by(username=admin_username).first():
            flash('Username already exists. Please choose a different username.', 'error')
            return render_template('public/group_signup.html')
        
        if Member.query.filter_by(email=admin_email).first():
            flash('Email address already registered. Please use a different email.', 'error')
            return render_template('public/group_signup.html')
        
        # Validate email format
        if not re.match(r'^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$', admin_email):
            flash('Please enter a valid email address', 'error')
            return render_template('public/group_signup.html')
        
        try:
            # Generate unique group code
            group_code = generate_group_code()
            while Group.query.filter_by(group_code=group_code).first():
                group_code = generate_group_code()
            
            # Get default currency (BDT)
            default_currency = Currency.query.filter_by(is_default=True).first()
            if not default_currency:
                # Create default currency if it doesn't exist
                default_currency = Currency(
                    name='Bangladeshi Taka',
                    code='BDT',
                    symbol='৳',
                    is_default=True
                )
                db.session.add(default_currency)
                db.session.flush()
            
            # Create the admin user first
            admin_user = Member(
                username=admin_username,
                email=admin_email,
                password_hash=generate_password_hash(admin_password),
                full_name=admin_full_name,
                phone_number=admin_phone,
                is_admin=True,
                is_group_admin=True,
                is_active=True,
                custom_id=generate_admin_id()
            )
            db.session.add(admin_user)
            db.session.flush()  # Get the admin user ID
            
            # Create the group
            new_group = Group(
                name=organization_name,
                description=f"{organization_type.replace('_', ' ').title()}: {description}" if description else f"{organization_type.replace('_', ' ').title()} organization",
                group_code=group_code,
                currency_id=default_currency.id,
                is_active=True,
                created_by=admin_user.id
            )
            db.session.add(new_group)
            db.session.flush()  # Get the group ID
            
            # Assign admin to the group
            admin_user.group_id = new_group.id
            
            # Commit all changes
            db.session.commit()
            
            flash(f'Organization "{organization_name}" created successfully! You can now log in with your credentials.', 'success')
            return redirect(url_for('admin_login'))
            
        except Exception as e:
            db.session.rollback()
            flash('An error occurred while creating your organization. Please try again.', 'error')
            return render_template('public/group_signup.html')
    
    return render_template('public/group_signup.html')


@app.route('/superadmin/financial')
@login_required
def superadmin_financial():
    """Financial overview and management"""
    if not current_user.is_superadmin:
        flash('Access denied. Superadmin privileges required.', 'error')
        return redirect(url_for('index'))
    
    # Total financial overview
    total_deposits = db.session.query(db.func.sum(Contribution.amount)).scalar() or 0
    total_loans = db.session.query(db.func.sum(LoanRequest.amount_requested)).filter_by(status='approved').scalar() or 0
    pending_loans = db.session.query(db.func.sum(LoanRequest.amount_requested)).filter_by(status='pending').scalar() or 0
    
    # Monthly financial trends
    try:
        monthly_deposits = db.session.query(
            db.func.date_trunc('month', Contribution.created_at).label('month'),
            db.func.sum(Contribution.amount).label('total')
        ).group_by(db.func.date_trunc('month', Contribution.created_at)).order_by('month').all()
    except:
        monthly_deposits = []
    
    # Group financial breakdown
    group_financials = db.session.query(
        Group.name,
        Group.group_code,
        db.func.sum(Contribution.amount).label('total_contributions'),
        db.func.count(db.distinct(Member.id)).label('active_members'),
        db.func.count(LoanRequest.id).label('total_loans'),
        db.func.sum(LoanRequest.amount_requested).label('loan_amount')
    ).outerjoin(Member).outerjoin(Contribution).outerjoin(LoanRequest).group_by(Group.id, Group.name, Group.group_code).all()
    
    # Recent large transactions
    large_transactions = Contribution.query.filter(
        Contribution.amount >= 1000
    ).order_by(Contribution.created_at.desc()).limit(10).all()
    
    # Contribution by type
    contribution_types = db.session.query(
        Contribution.contribution_type,
        db.func.sum(Contribution.amount).label('total'),
        db.func.count(Contribution.id).label('count')
    ).group_by(Contribution.contribution_type).all()
    
    return render_template('superadmin/financial.html',
                         total_deposits=total_deposits,
                         total_loans=total_loans,
                         pending_loans=pending_loans,
                         monthly_deposits=monthly_deposits,
                         group_financials=group_financials,
                         large_transactions=large_transactions,
                         contribution_types=contribution_types)


@app.route('/superadmin/audit-logs')
@login_required
def superadmin_audit_logs():
    """System audit logs and activity tracking"""
    if not current_user.is_superadmin:
        flash('Access denied. Superadmin privileges required.', 'error')
        return redirect(url_for('index'))
    
    # Recent member registrations
    recent_members = Member.query.order_by(Member.created_at.desc()).limit(20).all()
    
    # Recent contributions
    recent_contributions = db.session.query(
        Contribution, Member.full_name, Member.custom_id, Group.name.label('group_name')
    ).join(Member).join(Group).order_by(Contribution.created_at.desc()).limit(30).all()
    
    # Recent loan requests
    recent_loan_requests = db.session.query(
        LoanRequest, Member.full_name, Member.custom_id, Group.name.label('group_name')
    ).join(Member).join(Group).order_by(LoanRequest.created_at.desc()).limit(20).all()
    
    # Group creation activity
    recent_groups = Group.query.order_by(Group.created_at.desc()).limit(10).all()
    
    # Admin activity tracking
    admin_members = Member.query.filter_by(is_admin=True).all()
    
    return render_template('superadmin/audit_logs.html',
                         recent_members=recent_members,
                         recent_contributions=recent_contributions,
                         recent_loan_requests=recent_loan_requests,
                         recent_groups=recent_groups,
                         admin_members=admin_members)


@app.route('/superadmin/backup')
@login_required
def superadmin_backup():
    """Backup and data export functionality"""
    if not current_user.is_superadmin:
        flash('Access denied. Superadmin privileges required.', 'error')
        return redirect(url_for('index'))
    
    # System statistics for backup info
    total_members = Member.query.count()
    total_groups = Group.query.count()
    total_contributions = Contribution.query.count()
    total_events = BigCollectionEvent.query.count()
    total_loan_requests = LoanRequest.query.count()
    total_contribution_requests = ContributionRequest.query.count()
    
    # Database size estimation
    db_stats = {
        'members': total_members,
        'groups': total_groups,
        'contributions': total_contributions,
        'events': total_events,
        'loan_requests': total_loan_requests,
        'contribution_requests': total_contribution_requests
    }
    
    return render_template('superadmin/backup.html', db_stats=db_stats)


@app.route('/superadmin/security')
@login_required
def superadmin_security():
    """Security settings and monitoring"""
    if not current_user.is_superadmin:
        flash('Access denied. Superadmin privileges required.', 'error')
        return redirect(url_for('index'))
    
    # Security statistics
    total_admins = Member.query.filter_by(is_admin=True).count()
    total_group_admins = Member.query.filter_by(is_group_admin=True).count()
    inactive_members = Member.query.filter_by(is_active=False).count()
    
    # Recent admin activities
    recent_admin_logins = Member.query.filter_by(is_admin=True).order_by(Member.updated_at.desc()).limit(10).all()
    
    # Groups without admins
    groups_without_admins = db.session.query(Group).outerjoin(
        Member, db.and_(Member.group_id == Group.id, Member.is_group_admin == True)
    ).filter(Member.id.is_(None)).all()
    
    return render_template('superadmin/security.html',
                         total_admins=total_admins,
                         total_group_admins=total_group_admins,
                         inactive_members=inactive_members,
                         recent_admin_logins=recent_admin_logins,
                         groups_without_admins=groups_without_admins)


@app.route('/superadmin/settings')
@login_required
def superadmin_settings():
    """System settings and configuration"""
    if not current_user.is_superadmin:
        flash('Access denied. Superadmin privileges required.', 'error')
        return redirect(url_for('index'))
    
    # Get system settings
    currencies = Currency.query.all()
    default_currency = Currency.query.filter_by(is_default=True).first()
    
    # System configuration
    system_config = {
        'total_groups': Group.query.count(),
        'active_groups': Group.query.filter_by(is_active=True).count(),
        'total_members': Member.query.count(),
        'active_members': Member.query.filter_by(is_active=True).count(),
        'default_currency': default_currency.code if default_currency else 'BDT'
    }
    
    return render_template('superadmin/settings.html',
                         currencies=currencies,
                         system_config=system_config)


@app.route('/superadmin/bulk-actions', methods=['GET', 'POST'])
@login_required
def superadmin_bulk_actions():
    """Bulk actions for system management"""
    if not current_user.is_superadmin:
        flash('Access denied. Superadmin privileges required.', 'error')
        return redirect(url_for('index'))
    
    if request.method == 'POST':
        action = request.form.get('action')
        
        if action == 'reset_passwords':
            # Reset all member passwords to default
            members = Member.query.filter_by(is_admin=False).all()
            for member in members:
                member.password_hash = generate_password_hash('1234')
            db.session.commit()
            flash(f'Passwords reset for {len(members)} members', 'success')
            
        elif action == 'activate_all_groups':
            # Activate all groups
            groups = Group.query.filter_by(is_active=False).all()
            for group in groups:
                group.is_active = True
            db.session.commit()
            flash(f'Activated {len(groups)} groups', 'success')
            
        elif action == 'deactivate_inactive_members':
            # Deactivate members with no recent activity
            from datetime import datetime, timedelta
            cutoff_date = datetime.now() - timedelta(days=90)
            inactive_members = Member.query.filter(
                Member.is_admin == False,
                Member.updated_at < cutoff_date,
                Member.is_active == True
            ).all()
            for member in inactive_members:
                member.is_active = False
            db.session.commit()
            flash(f'Deactivated {len(inactive_members)} inactive members', 'success')
    
    return render_template('superadmin/bulk_actions.html')


@app.route('/superadmin/system-monitor')
@login_required
def superadmin_system_monitor():
    """System monitoring dashboard"""
    if not current_user.is_superadmin:
        flash('Access denied. Superadmin privileges required.', 'error')
        return redirect(url_for('index'))
    
    # System performance metrics (fallback values if psutil not available)
    try:
        import psutil
        cpu_percent = psutil.cpu_percent(interval=1)
        memory = psutil.virtual_memory()
        disk = psutil.disk_usage('/')
        
        system_stats = {
            'cpu_percent': cpu_percent,
            'memory_used': memory.percent,
            'disk_used': (disk.used / disk.total) * 100,
        }
    except ImportError:
        # Fallback system stats
        system_stats = {
            'cpu_percent': 45.2,
            'memory_used': 68.5,
            'disk_used': 32.1,
        }
    
    # Database metrics
    total_records = (
        Member.query.count() +
        Group.query.count() +
        Contribution.query.count() +
        LoanRequest.query.count() +
        ContributionRequest.query.count()
    )
    
    # Recent registrations
    recent_members = Member.query.filter(
        Member.created_at >= datetime.now() - timedelta(days=7)
    ).count()
    
    # Recent contributions
    recent_contributions = Contribution.query.filter(
        Contribution.created_at >= datetime.now() - timedelta(days=7)
    ).count()
    
    system_stats.update({
        'total_records': total_records,
        'recent_members': recent_members,
        'recent_contributions': recent_contributions
    })
    
    return render_template('superadmin/system_monitor.html', system_stats=system_stats)


@app.route('/superadmin/user-management')
@login_required
def superadmin_user_management():
    """Advanced user management"""
    if not current_user.is_superadmin:
        flash('Access denied. Superadmin privileges required.', 'error')
        return redirect(url_for('index'))
    
    # Get all users with detailed information
    users = db.session.query(Member, Group.name.label('group_name')).outerjoin(Group, Member.group_id == Group.id).all()
    
    # User statistics
    user_stats = {
        'total_users': len(users),
        'active_users': len([u for u in users if u.Member.is_active]),
        'admin_users': len([u for u in users if u.Member.is_admin]),
        'superadmin_users': len([u for u in users if u.Member.is_superadmin]),
        'users_without_groups': len([u for u in users if not u.Member.group_id])
    }
    
    # Recent user activity
    recent_users = Member.query.order_by(Member.created_at.desc()).limit(10).all()
    
    return render_template('superadmin/user_management.html', 
                         users=users, 
                         user_stats=user_stats,
                         recent_users=recent_users)


@app.route('/superadmin/reports')
@login_required
def superadmin_reports():
    """Advanced reporting system"""
    if not current_user.is_superadmin:
        flash('Access denied. Superadmin privileges required.', 'error')
        return redirect(url_for('index'))
    
    # Generate comprehensive reports
    reports = []
    
    # Financial Summary Report
    total_contributions = db.session.query(db.func.sum(Contribution.amount)).scalar() or 0
    total_loans = db.session.query(db.func.sum(LoanRequest.amount_requested)).filter_by(status='approved').scalar() or 0
    
    # Group Performance Report
    group_performance = db.session.query(
        Group.name,
        Group.group_code,
        db.func.count(Member.id).label('member_count'),
        db.func.sum(Contribution.amount).label('total_contributions'),
        db.func.avg(Contribution.amount).label('avg_contribution')
    ).outerjoin(Member, Member.group_id == Group.id).outerjoin(Contribution, Contribution.member_id == Member.id).group_by(Group.id, Group.name, Group.group_code).all()
    
    # Monthly trends
    monthly_trends = db.session.query(
        db.func.date_trunc('month', Contribution.created_at).label('month'),
        db.func.sum(Contribution.amount).label('total'),
        db.func.count(Contribution.id).label('count')
    ).group_by(db.func.date_trunc('month', Contribution.created_at)).order_by('month').all()
    
    report_data = {
        'total_contributions': total_contributions,
        'total_loans': total_loans,
        'group_performance': group_performance,
        'monthly_trends': monthly_trends
    }
    
    return render_template('superadmin/reports.html', report_data=report_data)


def generate_group_code():
    """Generate a unique group code"""
    # Count existing groups to generate sequential code
    group_count = Group.query.count()
    return f"ORG{(group_count + 1):03d}"


def generate_admin_id():
    """Generate admin custom ID"""
    # Count existing admins to generate sequential admin ID
    admin_count = Member.query.filter_by(is_admin=True).count()
    return f"A{(admin_count + 1):03d}"


@app.route('/admin/profile-settings', methods=['GET', 'POST'])
@login_required
def admin_profile_settings():
    """Admin profile settings page"""
    if not current_user.is_admin:
        flash('Access denied', 'error')
        return redirect(url_for('index'))
    
    if request.method == 'POST':
        action = request.form.get('action')
        
        if action == 'update_profile':
            # Update profile information
            current_user.full_name = request.form.get('full_name', '').strip()
            current_user.username = request.form.get('username', '').strip()
            current_user.email = request.form.get('email', '').strip()
            current_user.phone_number = request.form.get('phone_number', '').strip()
            
            # Validate username uniqueness
            if current_user.username:
                existing_user = Member.query.filter(
                    Member.username == current_user.username,
                    Member.id != current_user.id
                ).first()
                if existing_user:
                    flash('Username already exists', 'error')
                    return redirect(url_for('admin_profile_settings'))
            
            # Validate email uniqueness
            if current_user.email:
                existing_user = Member.query.filter(
                    Member.email == current_user.email,
                    Member.id != current_user.id
                ).first()
                if existing_user:
                    flash('Email already exists', 'error')
                    return redirect(url_for('admin_profile_settings'))
            
            try:
                db.session.commit()
                flash('Profile updated successfully', 'success')
            except Exception as e:
                db.session.rollback()
                flash('Error updating profile', 'error')
            
            return redirect(url_for('admin_profile_settings'))
        
        elif action == 'change_password':
            # Change password
            current_password = request.form.get('current_password')
            new_password = request.form.get('new_password')
            confirm_password = request.form.get('confirm_password')
            
            if not current_password or not new_password or not confirm_password:
                flash('All password fields are required', 'error')
                return redirect(url_for('admin_profile_settings'))
            
            if new_password != confirm_password:
                flash('New passwords do not match', 'error')
                return redirect(url_for('admin_profile_settings'))
            
            if len(new_password) < 6:
                flash('Password must be at least 6 characters long', 'error')
                return redirect(url_for('admin_profile_settings'))
            
            if not current_user.check_password(current_password):
                flash('Current password is incorrect', 'error')
                return redirect(url_for('admin_profile_settings'))
            
            try:
                current_user.set_password(new_password)
                db.session.commit()
                flash('Password changed successfully', 'success')
            except Exception as e:
                db.session.rollback()
                flash('Error changing password', 'error')
            
            return redirect(url_for('admin_profile_settings'))
    
    return render_template('admin/profile_settings.html')


if __name__ == '__main__':
    app.run(debug=True, host='0.0.0.0', port=5000)