# ================= bonus.py =================

from flask import Blueprint, request, jsonify, render_template, flash, redirect, url_for,send_file
from flask_login import login_required, current_user
from datetime import datetime, date, timedelta
from sqlalchemy import or_
import io
import pandas as pd
from models import (
    db, BonusPolicy, BonusTenureSlab, BonusRun, BonusEntry,
    BonusPayrollPosting, Employee, Attendance, LeaveRequest,
    Holiday, WeekendSettings, Payroll, FrozenSalaryComponent
)

bonus_bp = Blueprint('bonus', __name__)

# ---------- FY HELPERS ----------
def get_fy_dates(fy_label):
    start_year = int(fy_label.split('-')[0])
    end_year = start_year + 1
    return date(start_year, 4, 1), date(end_year, 3, 31)

def _build_bonus_export_dataframe(run, policy, entries):
    rows = []

    for i, entry in enumerate(entries, start=1):
        rows.append({
            "SN": i,
            "Employee ID": entry.employee_id,
            "Employee Name": entry.employee_name,
            "Date of Joining": entry.doj.strftime("%Y-%m-%d") if entry.doj else "",
            "Worked Days": entry.worked_days or 0,
            "Tenure Years": entry.tenure_years or 0,
            "Slab": entry.slab_label or "",
            "Base Amount": float(entry.base_amount or 0),
            "Override Amount": float(entry.override_amount or 0) if entry.override_amount is not None else "",
            "Final Amount": float(entry.final_amount or 0),
            "Policy Name": policy.name,
            "Bonus Type": policy.bonus_type,
            "Calculation Mode": policy.calculation_mode,
            "FY": run.fy_label,
            "Payout Month": run.payout_month,
            "Run Status": run.status,
        })

    return pd.DataFrame(rows)

def get_employee_bonus_window(employee, fy_start, fy_end):
    start_date = max(fy_start, employee.date_of_joining)

    if employee.exit_date:
        end_date = min(fy_end, employee.exit_date)
    else:
        end_date = fy_end

    if start_date > end_date:
        return None, None

    return start_date, end_date


def get_saturday_setting(weekend_settings, day):
    if not weekend_settings:
        return None

    week_number = (day.day - 1) // 7 + 1
    slots = ["first", "second", "third", "fourth", "fifth"]
    if week_number < 1 or week_number > 5:
        return None

    attr = f"{slots[week_number - 1]}_saturday"
    return getattr(weekend_settings, attr, None)


def is_weekend_day(day, weekend_settings):
    if not weekend_settings:
        return False

    if day.weekday() == 6:  # Sunday
        return weekend_settings.sunday == 'off'

    if day.weekday() == 5:  # Saturday
        sat_setting = get_saturday_setting(weekend_settings, day)
        return sat_setting == 'off'

    return False


def get_holiday_dates(employee, start_date, end_date):
    holidays = Holiday.query.filter(
        Holiday.date >= start_date,
        Holiday.date <= end_date,
        or_(Holiday.location == None, Holiday.location == employee.location)
    ).all()
    return {h.date for h in holidays}


def get_attendance_map(employee_id, start_date, end_date):
    records = Attendance.query.filter(
        Attendance.employee_id == employee_id,
        Attendance.date >= start_date,
        Attendance.date <= end_date
    ).all()
    return {a.date: a for a in records}


def get_approved_leave_dates(employee_id, start_date, end_date):
    approved_statuses = ['approved', 'hr_approved', 'Approved', 'HR Approved']

    leaves = LeaveRequest.query.filter(
        LeaveRequest.employee_id == employee_id,
        LeaveRequest.status.in_(approved_statuses),
        LeaveRequest.start_date <= end_date,
        LeaveRequest.end_date >= start_date
    ).all()

    leave_dates = {}
    for leave in leaves:
        current = max(leave.start_date, start_date)
        leave_end = min(leave.end_date, end_date)

        while current <= leave_end:
            if leave.half_day and leave.start_date == leave.end_date:
                leave_dates[current] = 0.5
            else:
                leave_dates[current] = 1.0
            current += timedelta(days=1)

    return leave_dates

def calculate_worked_days_for_bonus(employee, fy_start, fy_end):
    start_date, end_date = get_employee_bonus_window(employee, fy_start, fy_end)
    if not start_date or not end_date:
        return 0.0, 0

    weekend_settings = WeekendSettings.query.order_by(WeekendSettings.updated_on.desc()).first()
    holiday_dates = get_holiday_dates(employee, start_date, end_date)
    attendance_map = get_attendance_map(employee.id, start_date, end_date)
    approved_leave_dates = get_approved_leave_dates(employee.id, start_date, end_date)

    worked_days = 0.0
    total_days_in_window = (end_date - start_date).days + 1
    current = start_date

    while current <= end_date:
        att = attendance_map.get(current)

        if att:
            remark = (att.remarks or '').strip().lower()
            status = (att.status or '').strip().lower()

            if remark == 'present':
                worked_days += 1.0
            elif remark == 'half day':
                worked_days += 0.5
            elif remark == 'leave':
                worked_days += 1.0
            elif remark == 'holiday':
                worked_days += 1.0
            elif remark == 'weekend':
                worked_days += 1.0
            elif remark == 'lwp':
                worked_days += 0.0
            else:
                if status in ['present', 'paid_leave']:
                    worked_days += 1.0
                elif status == 'half_day':
                    worked_days += 0.5
                else:
                    worked_days += 0.0

        elif current in approved_leave_dates:
            worked_days += approved_leave_dates[current]

        elif current in holiday_dates:
            worked_days += 1.0

        elif is_weekend_day(current, weekend_settings):
            worked_days += 1.0

        current += timedelta(days=1)

    return round(worked_days, 1), total_days_in_window

def calculate_final_bonus_amount(policy, base_amount, worked_days, total_fy_days):
    if worked_days < float(policy.min_worked_days or 0):
        return 0.0

    if policy.prorate_by_worked_days:
        if total_fy_days <= 0:
            return 0.0
        return round((base_amount * worked_days) / total_fy_days, 2)

    return round(base_amount, 2)


def get_policy_base_amount(policy, tenure_years):
    slab_label = None
    base_amount = 0.0

    if policy.calculation_mode == 'slab_wise':
        slab = get_matching_bonus_slab(policy.id, tenure_years)
        if slab:
            slab_label = slab.label
            base_amount = float(slab.amount or 0)

    elif policy.calculation_mode == 'fixed_amount':
        slab = BonusTenureSlab.query.filter_by(policy_id=policy.id).order_by(BonusTenureSlab.id.asc()).first()
        if slab:
            slab_label = slab.label
            base_amount = float(slab.amount or 0)

    return slab_label, round(base_amount, 2)

def calculate_completed_years(doj, as_on_date):
    if not doj or doj > as_on_date:
        return 0

    years = as_on_date.year - doj.year
    if (as_on_date.month, as_on_date.day) < (doj.month, doj.day):
        years -= 1

    return max(years, 0)


def get_matching_bonus_slab(policy_id, tenure_years):
    slabs = BonusTenureSlab.query.filter_by(policy_id=policy_id).order_by(BonusTenureSlab.from_years.asc()).all()

    for slab in slabs:
        if slab.to_years is None:
            if tenure_years >= slab.from_years:
                return slab
        else:
            if slab.from_years <= tenure_years < slab.to_years:
                return slab

    return None

# ---------- ROUTES ----------

@bonus_bp.route('/bonus/policy', methods=['POST'])
def create_policy():
    data = request.json
    policy = BonusPolicy(
        name=data['name'],
        bonus_type=data['bonus_type'],
        calculation_mode=data['calculation_mode'],
        prorate_by_worked_days=data.get('prorate_by_worked_days', False),
        min_worked_days=data.get('min_worked_days', 0)
    )
    db.session.add(policy)
    db.session.commit()
    return jsonify({'id': policy.id})

@bonus_bp.route('/api/bonus/policy/<int:policy_id>/slabs', methods=['POST'])
@login_required
def add_slab(policy_id):
    data = request.get_json() or {}

    slab = BonusTenureSlab(
        policy_id=policy_id,
        label=data['label'],
        from_years=data['from_years'],
        to_years=data.get('to_years'),
        amount=data['amount']
    )
    db.session.add(slab)
    db.session.commit()
    return jsonify({'message': 'slab added'})

@bonus_bp.route('/bonus/run', methods=['POST'])
@login_required
def create_run():
    data = request.get_json() or {}

    policy_id = data.get('policy_id')
    fy_label = data.get('fy_label')
    payout_month = data.get('payout_month')

    if not policy_id or not fy_label or not payout_month:
        return jsonify({'error': 'policy_id, fy_label and payout_month are required'}), 400

    existing = BonusRun.query.filter_by(
        policy_id=policy_id,
        fy_label=fy_label,
        payout_month=payout_month
    ).first()
    if existing:
        return jsonify({'error': 'Bonus run already exists for this policy/FY/payout month'}), 400

    fy_start, fy_end = get_fy_dates(fy_label)

    run = BonusRun(
        policy_id=policy_id,
        fy_label=fy_label,
        fy_start_date=fy_start,
        fy_end_date=fy_end,
        payout_month=payout_month,
        status='draft'
    )
    db.session.add(run)
    db.session.commit()

    return jsonify({'run_id': run.id, 'status': run.status})

@bonus_bp.route('/bonus/run/<int:run_id>/preview', methods=['GET'])
@login_required
def preview_bonus_run(run_id):
    run = BonusRun.query.get_or_404(run_id)
    policy = BonusPolicy.query.get_or_404(run.policy_id)

    if run.status in ['frozen', 'posted']:
        existing = BonusEntry.query.filter_by(bonus_run_id=run.id).all()
        return jsonify([
            {
                'entry_id': e.id,
                'employee_id': e.employee_id,
                'employee_name': e.employee_name,
                'doj': e.doj.strftime('%Y-%m-%d') if e.doj else None,
                'worked_days': e.worked_days,
                'tenure_years': e.tenure_years,
                'slab_label': e.slab_label,
                'base_amount': e.base_amount,
                'override_amount': getattr(e, 'override_amount', None),
                'final_amount': e.final_amount
            }
            for e in existing
        ])

    existing_entries = {
        e.employee_id: e for e in BonusEntry.query.filter_by(bonus_run_id=run.id).all()
    }

    employees = Employee.query.filter_by(status='active').all()

    for emp in employees:
        start_date, end_date = get_employee_bonus_window(emp, run.fy_start_date, run.fy_end_date)
        if not start_date or not end_date:
            continue

        worked_days, total_days_in_window = calculate_worked_days_for_bonus(
            emp, run.fy_start_date, run.fy_end_date
        )
        tenure_years = calculate_completed_years(emp.date_of_joining, run.fy_end_date)
        slab_label, base_amount = get_policy_base_amount(policy, tenure_years)
        calculated_final = calculate_final_bonus_amount(
            policy, base_amount, worked_days, total_days_in_window
        )

        old = existing_entries.get(emp.id)

        if old:
            old.employee_name = emp.full_name
            old.doj = emp.date_of_joining
            old.worked_days = worked_days
            old.tenure_years = tenure_years
            old.slab_label = slab_label
            old.base_amount = base_amount

            if not getattr(old, 'is_manual_override', False):
                old.final_amount = calculated_final
        else:
            entry = BonusEntry(
                bonus_run_id=run.id,
                employee_id=emp.id,
                employee_name=emp.full_name,
                doj=emp.date_of_joining,
                worked_days=worked_days,
                tenure_years=tenure_years,
                slab_label=slab_label,
                base_amount=base_amount,
                final_amount=calculated_final
            )
            db.session.add(entry)

    db.session.commit()

    rows = BonusEntry.query.filter_by(bonus_run_id=run.id).all()
    return jsonify([
        {
            'entry_id': e.id,
            'employee_id': e.employee_id,
            'employee_name': e.employee_name,
            'doj': e.doj.strftime('%Y-%m-%d') if e.doj else None,
            'worked_days': e.worked_days,
            'tenure_years': e.tenure_years,
            'slab_label': e.slab_label,
            'base_amount': e.base_amount,
            'override_amount': getattr(e, 'override_amount', None),
            'final_amount': e.final_amount
        }
        for e in rows
    ])

@bonus_bp.route('/bonus/run/<int:run_id>/freeze', methods=['POST'])
@login_required
def freeze(run_id):
    run = BonusRun.query.get_or_404(run_id)

    if run.status == 'posted':
        return jsonify({'error': 'Posted run cannot be frozen again'}), 400

    entries = BonusEntry.query.filter_by(bonus_run_id=run.id).all()
    if not entries:
        return jsonify({'error': 'No bonus entries found to freeze'}), 400

    run.status = 'frozen'
    db.session.commit()
    return jsonify({'message': 'frozen', 'run_id': run.id, 'status': run.status})

@bonus_bp.route('/bonus/entry/<int:entry_id>/override', methods=['POST'])
@login_required
def override_bonus_entry(entry_id):
    entry = BonusEntry.query.get_or_404(entry_id)
    run = BonusRun.query.get_or_404(entry.bonus_run_id)

    if run.status in ['frozen', 'posted']:
        return jsonify({'error': 'Frozen/posted run cannot be edited'}), 400

    data = request.get_json() or {}
    override_amount = data.get('override_amount')

    if override_amount is None:
        return jsonify({'error': 'override_amount is required'}), 400

    try:
        override_amount = float(override_amount)
    except Exception:
        return jsonify({'error': 'Invalid override_amount'}), 400

    if hasattr(entry, 'override_amount'):
        entry.override_amount = round(override_amount, 2)
    if hasattr(entry, 'is_manual_override'):
        entry.is_manual_override = True

    entry.final_amount = round(override_amount, 2)

    db.session.commit()

    return jsonify({
        'message': 'Override saved',
        'entry_id': entry.id,
        'final_amount': entry.final_amount
    })

@bonus_bp.route('/bonus/run/<int:run_id>/post-to-payroll', methods=['POST'])
@login_required
def post_to_payroll(run_id):
    run = BonusRun.query.get_or_404(run_id)
    policy = BonusPolicy.query.get_or_404(run.policy_id)

    if run.status != 'frozen':
        return jsonify({'error': 'Only frozen runs can be posted'}), 400

    entries = BonusEntry.query.filter_by(bonus_run_id=run.id).all()
    component_name = 'Statutory Bonus' if policy.bonus_type == 'statutory' else 'Customary Bonus'

    posted_count = 0
    skipped = []

    for entry in entries:
        if float(entry.final_amount or 0) <= 0:
            skipped.append({
                'employee_id': entry.employee_id,
                'employee_name': entry.employee_name,
                'reason': 'Final amount is zero'
            })
            continue

        existing_post = BonusPayrollPosting.query.filter_by(
            bonus_entry_id=entry.id,
            payroll_month=run.payout_month
        ).first()
        if existing_post:
            skipped.append({
                'employee_id': entry.employee_id,
                'employee_name': entry.employee_name,
                'reason': 'Already posted'
            })
            continue

        payroll = Payroll.query.filter_by(
            employee_id=entry.employee_id,
            month=run.payout_month
        ).first()

        if not payroll:
            skipped.append({
                'employee_id': entry.employee_id,
                'employee_name': entry.employee_name,
                'reason': f'Payroll not found for {run.payout_month}'
            })
            continue

        frozen_component = FrozenSalaryComponent(
            employee_id=entry.employee_id,
            month=run.payout_month,
            component_type='earning',
            head_name=component_name,
            amount=round(float(entry.final_amount or 0), 2)
        )
        db.session.add(frozen_component)

        posting = BonusPayrollPosting(
            bonus_entry_id=entry.id,
            employee_id=entry.employee_id,
            payroll_month=run.payout_month,
            component_name=component_name,
            posted_amount=round(float(entry.final_amount or 0), 2)
        )
        db.session.add(posting)

        payroll.gross_pay = round(float(payroll.gross_pay or 0) + float(entry.final_amount or 0), 2)
        payroll.net_pay = round(float(payroll.net_pay or 0) + float(entry.final_amount or 0), 2)

        posted_count += 1

    if posted_count > 0:
        run.status = 'posted'

    db.session.commit()

    return jsonify({
        'message': 'Posting completed',
        'run_id': run.id,
        'status': run.status,
        'posted_count': posted_count,
        'skipped': skipped
    })

@bonus_bp.route('/bonus/run/<int:run_id>/view', methods=['GET'])
@login_required
def view_bonus_run(run_id):
    run = BonusRun.query.get_or_404(run_id)
    policy = BonusPolicy.query.get_or_404(run.policy_id)

    entries = BonusEntry.query.filter_by(bonus_run_id=run.id).order_by(BonusEntry.employee_name.asc()).all()

    return render_template(
        'bonus_preview.html',
        run=run,
        policy=policy,
        entries=entries
    )

@bonus_bp.route('/bonus/run/<int:run_id>/generate', methods=['GET'])
@login_required
def generate_bonus_preview(run_id):
    run = BonusRun.query.get_or_404(run_id)
    policy = BonusPolicy.query.get_or_404(run.policy_id)

    if run.status in ['frozen', 'posted']:
        return redirect(url_for('bonus.view_bonus_run', run_id=run.id))

    existing_entries = {
        e.employee_id: e for e in BonusEntry.query.filter_by(bonus_run_id=run.id).all()
    }

    employees = Employee.query.filter_by(status='active').all()

    for emp in employees:
        start_date, end_date = get_employee_bonus_window(emp, run.fy_start_date, run.fy_end_date)
        if not start_date or not end_date:
            continue

        worked_days, total_days_in_window = calculate_worked_days_for_bonus(
            emp, run.fy_start_date, run.fy_end_date
        )
        tenure_years = calculate_completed_years(emp.date_of_joining, run.fy_end_date)
        slab_label, base_amount = get_policy_base_amount(policy, tenure_years)
        calculated_final = calculate_final_bonus_amount(
            policy, base_amount, worked_days, total_days_in_window
        )

        old = existing_entries.get(emp.id)

        if old:
            old.employee_name = emp.full_name
            old.doj = emp.date_of_joining
            old.worked_days = worked_days
            old.tenure_years = tenure_years
            old.slab_label = slab_label
            old.base_amount = base_amount

            if not getattr(old, 'is_manual_override', False):
                old.final_amount = calculated_final
        else:
            entry = BonusEntry(
                bonus_run_id=run.id,
                employee_id=emp.id,
                employee_name=emp.full_name,
                doj=emp.date_of_joining,
                worked_days=worked_days,
                tenure_years=tenure_years,
                slab_label=slab_label,
                base_amount=base_amount,
                final_amount=calculated_final
            )
            db.session.add(entry)

    db.session.commit()
    flash('Bonus preview generated successfully.', 'success')
    return redirect(url_for('bonus.view_bonus_run', run_id=run.id))

@bonus_bp.route('/bonus/policies', methods=['GET', 'POST'])
@login_required
def bonus_policies():
    if request.method == 'POST':
        name = request.form.get('name')
        bonus_type = request.form.get('bonus_type')
        calculation_mode = request.form.get('calculation_mode')
        prorate_by_worked_days = True if request.form.get('prorate_by_worked_days') == 'on' else False
        min_worked_days = request.form.get('min_worked_days') or 0
        is_active = True if request.form.get('is_active') == 'on' else False

        if not name or not bonus_type or not calculation_mode:
            flash('Please fill all required fields.', 'danger')
            return redirect(url_for('bonus.bonus_policies'))

        policy = BonusPolicy(
            name=name,
            bonus_type=bonus_type,
            calculation_mode=calculation_mode,
            prorate_by_worked_days=prorate_by_worked_days,
            min_worked_days=int(min_worked_days),
            is_active=is_active
        )
        db.session.add(policy)
        db.session.commit()
        flash('Bonus policy created successfully.', 'success')
        return redirect(url_for('bonus.bonus_policies'))

    policies = BonusPolicy.query.order_by(BonusPolicy.id.desc()).all()
    return render_template('bonus_policies.html', policies=policies)

@bonus_bp.route('/bonus/policy/<int:policy_id>/slabs', methods=['GET', 'POST'])
@login_required
def bonus_slabs(policy_id):
    policy = BonusPolicy.query.get_or_404(policy_id)

    if request.method == 'POST':
        label = request.form.get('label')
        from_years = request.form.get('from_years')
        to_years = request.form.get('to_years')
        amount = request.form.get('amount')

        if not label or from_years == '' or amount == '':
            flash('Please fill all required slab fields.', 'danger')
            return redirect(url_for('bonus.bonus_slabs', policy_id=policy.id))

        slab = BonusTenureSlab(
            policy_id=policy.id,
            label=label,
            from_years=int(from_years),
            to_years=int(to_years) if to_years not in [None, ''] else None,
            amount=float(amount)
        )
        db.session.add(slab)
        db.session.commit()
        flash('Slab added successfully.', 'success')
        return redirect(url_for('bonus.bonus_slabs', policy_id=policy.id))

    slabs = BonusTenureSlab.query.filter_by(policy_id=policy.id).order_by(BonusTenureSlab.from_years.asc()).all()
    return render_template('bonus_slabs.html', policy=policy, slabs=slabs)

@bonus_bp.route('/bonus/runs', methods=['GET', 'POST'])
@login_required
def bonus_runs():
    if request.method == 'POST':
        policy_id = request.form.get('policy_id')
        fy_label = request.form.get('fy_label')
        payout_month = request.form.get('payout_month')

        if not policy_id or not fy_label or not payout_month:
            flash('Please fill all required fields.', 'danger')
            return redirect(url_for('bonus.bonus_runs'))

        existing = BonusRun.query.filter_by(
            policy_id=int(policy_id),
            fy_label=fy_label,
            payout_month=payout_month
        ).first()

        if existing:
            flash('Bonus run already exists for this policy/FY/payout month.', 'warning')
            return redirect(url_for('bonus.bonus_runs'))

        fy_start, fy_end = get_fy_dates(fy_label)

        run = BonusRun(
            policy_id=int(policy_id),
            fy_label=fy_label,
            fy_start_date=fy_start,
            fy_end_date=fy_end,
            payout_month=payout_month,
            status='draft'
        )
        db.session.add(run)
        db.session.commit()

        flash('Bonus run created successfully.', 'success')
        return redirect(url_for('bonus.bonus_runs'))

    policies = BonusPolicy.query.filter_by(is_active=True).order_by(BonusPolicy.name.asc()).all()
    runs = BonusRun.query.order_by(BonusRun.id.desc()).all()

    run_rows = []
    for run in runs:
        policy = BonusPolicy.query.get(run.policy_id)
        entry_count = BonusEntry.query.filter_by(bonus_run_id=run.id).count()
        total_amount = db.session.query(
            db.func.coalesce(db.func.sum(BonusEntry.final_amount), 0)
        ).filter(
            BonusEntry.bonus_run_id == run.id
        ).scalar() or 0

        run_rows.append({
            "run": run,
            "policy_name": policy.name if policy else "",
            "policy_type": policy.bonus_type if policy else "",
            "entry_count": entry_count,
            "total_amount": round(float(total_amount), 2)
        })

    return render_template(
        'bonus_runs.html',
        policies=policies,
        run_rows=run_rows
    )

@bonus_bp.route('/bonus/run/<int:run_id>/undo-post-to-payroll', methods=['POST'])
@login_required
def undo_post_to_payroll(run_id):
    run = BonusRun.query.get_or_404(run_id)
    policy = BonusPolicy.query.get_or_404(run.policy_id)

    if run.status != 'posted':
        return jsonify({'error': 'Only posted runs can be undone'}), 400

    component_name = 'Statutory Bonus' if policy.bonus_type == 'statutory' else 'Customary Bonus'

    postings = BonusPayrollPosting.query.filter_by(
        payroll_month=run.payout_month
    ).join(
        BonusEntry, BonusEntry.id == BonusPayrollPosting.bonus_entry_id
    ).filter(
        BonusEntry.bonus_run_id == run.id
    ).all()

    if not postings:
        return jsonify({'error': 'No posted bonus records found for this run'}), 400

    undone_count = 0

    for posting in postings:
        payroll = Payroll.query.filter_by(
            employee_id=posting.employee_id,
            month=posting.payroll_month
        ).first()

        if payroll:
            payroll.gross_pay = round(float(payroll.gross_pay or 0) - float(posting.posted_amount or 0), 2)
            payroll.net_pay = round(float(payroll.net_pay or 0) - float(posting.posted_amount or 0), 2)

            # safety: avoid negative accidental values
            if payroll.gross_pay < 0:
                payroll.gross_pay = 0
            if payroll.net_pay < 0:
                payroll.net_pay = 0

        FrozenSalaryComponent.query.filter_by(
            employee_id=posting.employee_id,
            month=posting.payroll_month,
            component_type='earning',
            head_name=component_name,
            amount=posting.posted_amount
        ).delete()

        db.session.delete(posting)
        undone_count += 1

    run.status = 'draft'
    db.session.commit()

    return jsonify({
        'message': 'Posted bonus reversed successfully',
        'run_id': run.id,
        'status': run.status,
        'undone_count': undone_count
    })

@bonus_bp.route('/bonus/run/<int:run_id>/download-preview', methods=['GET'])
@login_required
def download_bonus_preview_excel(run_id):
    run = BonusRun.query.get_or_404(run_id)
    policy = BonusPolicy.query.get_or_404(run.policy_id)

    entries = BonusEntry.query.filter_by(
        bonus_run_id=run.id
    ).order_by(BonusEntry.employee_name.asc()).all()

    if not entries:
        flash('No bonus entries found for export.', 'warning')
        return redirect(url_for('bonus.view_bonus_run', run_id=run.id))

    df = _build_bonus_export_dataframe(run, policy, entries)

    output = io.BytesIO()
    with pd.ExcelWriter(output, engine='openpyxl') as writer:
        df.to_excel(writer, index=False, sheet_name='Bonus Preview')

    output.seek(0)

    filename = f"bonus_preview_{run.fy_label}_{run.payout_month}_run_{run.id}.xlsx"
    return send_file(
        output,
        as_attachment=True,
        download_name=filename,
        mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    )

@bonus_bp.route('/bonus/run/<int:run_id>/download-frozen', methods=['GET'])
@login_required
def download_frozen_bonus_excel(run_id):
    run = BonusRun.query.get_or_404(run_id)
    policy = BonusPolicy.query.get_or_404(run.policy_id)

    if run.status not in ['frozen', 'posted']:
        flash('Frozen bonus export is available only for frozen or posted runs.', 'warning')
        return redirect(url_for('bonus.view_bonus_run', run_id=run.id))

    entries = BonusEntry.query.filter_by(
        bonus_run_id=run.id
    ).order_by(BonusEntry.employee_name.asc()).all()

    if not entries:
        flash('No bonus entries found for export.', 'warning')
        return redirect(url_for('bonus.view_bonus_run', run_id=run.id))

    df = _build_bonus_export_dataframe(run, policy, entries)

    output = io.BytesIO()
    with pd.ExcelWriter(output, engine='openpyxl') as writer:
        df.to_excel(writer, index=False, sheet_name='Frozen Bonus')

    output.seek(0)

    filename = f"frozen_bonus_{run.fy_label}_{run.payout_month}_run_{run.id}.xlsx"
    return send_file(
        output,
        as_attachment=True,
        download_name=filename,
        mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    )

@bonus_bp.route('/bonus/dashboard', methods=['GET'])
@login_required
def bonus_dashboard():
    total_policies = BonusPolicy.query.count()
    active_policies = BonusPolicy.query.filter_by(is_active=True).count()

    total_runs = BonusRun.query.count()
    draft_runs = BonusRun.query.filter_by(status='draft').count()
    frozen_runs = BonusRun.query.filter_by(status='frozen').count()
    posted_runs = BonusRun.query.filter_by(status='posted').count()

    latest_runs = BonusRun.query.order_by(BonusRun.id.desc()).limit(10).all()

    run_summaries = []
    grand_total_bonus = 0

    for run in latest_runs:
        policy = BonusPolicy.query.get(run.policy_id)
        total_amount = db.session.query(db.func.coalesce(db.func.sum(BonusEntry.final_amount), 0)).filter(
            BonusEntry.bonus_run_id == run.id
        ).scalar() or 0

        employee_count = BonusEntry.query.filter_by(bonus_run_id=run.id).count()

        grand_total_bonus += float(total_amount)

        run_summaries.append({
            "run": run,
            "policy_name": policy.name if policy else "",
            "bonus_type": policy.bonus_type if policy else "",
            "employee_count": employee_count,
            "total_amount": round(float(total_amount), 2)
        })

    return render_template(
        'bonus_dashboard.html',
        total_policies=total_policies,
        active_policies=active_policies,
        total_runs=total_runs,
        draft_runs=draft_runs,
        frozen_runs=frozen_runs,
        posted_runs=posted_runs,
        grand_total_bonus=round(grand_total_bonus, 2),
        run_summaries=run_summaries
    )