# routes/exit.py
from __future__ import annotations

import os
from datetime import datetime
from typing import Iterable

from flask import (
    Blueprint, render_template, request, redirect, url_for, flash,send_file
)
import io
import pandas as pd
from flask_login import login_required, current_user
from werkzeug.utils import secure_filename

from models import (
    db, ExitRequest, HandoverDoc, HandoverDocViewer, ExitEvent, Employee, GratuitySetting, GratuityComponent, EarningHead, EmployeeSalaryStructures, Payroll, FrozenSalaryComponent, BonusEntry, 
    BonusRun, GratuityCalculation, GratuityDisbursement, FullFinalSettlement, AssetAllocation
)

exit_bp = Blueprint("exit", __name__, url_prefix="/exit")

# -----------------------------
# Small permission/utility stubs
# -----------------------------
def evaluate_gratuity_formula(formula, last_drawn_salary, eligible_service_years, service_years, remaining_months):
    allowed_names = {
        'last_drawn_salary': float(last_drawn_salary or 0),
        'eligible_service_years': float(eligible_service_years or 0),
        'service_years': float(service_years or 0),
        'remaining_months': float(remaining_months or 0),
        'min': min,
        'max': max,
        'round': round,
        'abs': abs
    }

    try:
        result = eval(formula, {"__builtins__": {}}, allowed_names)
        return round(float(result or 0), 2), None
    except Exception as e:
        return 0.0, str(e)

def user_role():
    role_obj = getattr(current_user, "role", None)
    if not role_obj:
        return ""
    if hasattr(role_obj, "name"):   # adjust if your field is different
        return (role_obj.name or "").lower()
    return str(role_obj).lower()

def has_role(*roles):
    r = user_role()
    return r in [role.lower() for role in roles]

def require_hr() -> bool:
    # loosen/tighten as needed (e.g., admin can act as HR)
    return has_role("hr", "admin")

def require_manager() -> bool:
    return has_role("manager", "hod", "admin")

def require_it() -> bool:
    return has_role("manager", "admin","it","it_support","it_admin","system_admin","it manager")

def save_exit_file(file_storage, exit_id: int) -> str | None:
    """
    Saves uploaded file to static/exit_documents/<exit_id>/<filename>
    Returns a path relative to /static (so url_for('static', filename=...) works).
    """
    if not file_storage or not file_storage.filename:
        return None
    uploads_dir = os.path.join("static", "exit_documents", str(exit_id))
    os.makedirs(uploads_dir, exist_ok=True)
    filename = secure_filename(file_storage.filename)
    full_path = os.path.join(uploads_dir, filename)
    file_storage.save(full_path)
    # store relative path (inside /static)
    return os.path.join("exit_documents", str(exit_id), filename).replace("\\", "/")

def get_employee_asset_allocations(employee_id: int):
    return (
        AssetAllocation.query
        .filter(
            AssetAllocation.employee_id == employee_id,
            AssetAllocation.status.in_(["pending", "allocated"])
        )
        .order_by(AssetAllocation.id.desc())
        .all()
    )

def pending_asset_handover_count(employee_id: int) -> int:
    return (
        AssetAllocation.query
        .filter(
            AssetAllocation.employee_id == employee_id,
            AssetAllocation.status.in_(["pending", "allocated"])
        )
        .count()
    )

def is_asset_handover_complete(employee_id: int) -> bool:
    return pending_asset_handover_count(employee_id) == 0
# -----------------------------
# HR: create exit
# -----------------------------
@exit_bp.route("/new", methods=["GET", "POST"])
@login_required
def create_exit():
    if request.method == "POST":
        if not require_hr():
            flash("You do not have permission to initiate exits.", "danger")
            return redirect(url_for("exit.create_exit"))

        try:
            emp_id = int(request.form["employee_id"])
            resignation_date = datetime.strptime(request.form["resignation_date"], "%Y-%m-%d").date()
            proposed_lwd = datetime.strptime(request.form["proposed_lwd"], "%Y-%m-%d").date()
        except Exception:
            flash("Invalid form input.", "danger")
            return redirect(url_for("exit.create_exit"))

        er = ExitRequest(
            employee_id=emp_id,
            created_by=current_user.id,
            resignation_date=resignation_date,
            proposed_lwd=proposed_lwd,
            status="initiated",
            remarks=request.form.get("remarks") or None,
        )
        db.session.add(er)
        db.session.flush()
        db.session.add(ExitEvent(exit_id=er.id, actor_id=current_user.id, action="created"))
        db.session.commit()

        flash("Exit initiated.", "success")
        return redirect(url_for("exit.view", exit_id=er.id))

    # GET
    employees = Employee.query.filter_by(status="active").all()
    return render_template("exit/new.html", employees=employees)

# -----------------------------
# Exit detail page
# -----------------------------
@exit_bp.route("/<int:exit_id>")
@login_required
def view(exit_id):
    er = ExitRequest.query.get_or_404(exit_id)
    docs = HandoverDoc.query.filter_by(exit_id=exit_id).order_by(HandoverDoc.uploaded_at.asc()).all()

    exiting_emp_id = er.employee_id

    employees = (
        Employee.query
        .filter(Employee.status == "active", Employee.id != exiting_emp_id)
        .order_by(Employee.full_name.asc())
        .all()
    )

    asset_allocations = get_employee_asset_allocations(er.employee_id)
    has_assets = len(asset_allocations) > 0
    asset_handover_done = is_asset_handover_complete(er.employee_id)

    has_docs = len(docs) > 0
    first_doc_time = docs[0].uploaded_at if has_docs else None

    all_ack = True
    latest_ack_ts = None
    if has_docs:
        for d in docs:
            for v in d.viewers:
                if v.can_view:
                    if v.acknowledged_at is None:
                        all_ack = False
                    else:
                        latest_ack_ts = max(latest_ack_ts or v.acknowledged_at, v.acknowledged_at)
    else:
        all_ack = False

    steps = [
        {"key": "manager_approved", "label": "Manager Accepted", "done": bool(er.manager_approved_at), "ts": er.manager_approved_at},
        {"key": "handover_uploaded", "label": "Handover Uploaded", "done": has_docs, "ts": first_doc_time},
        {"key": "all_ack", "label": "All Acknowledged", "done": all_ack, "ts": latest_ack_ts},
    ]

    if has_assets:
        steps.append({
            "key": "asset_handover",
            "label": "Asset Handover",
            "done": asset_handover_done,
            "ts": None
        })

    steps.extend([
        {"key": "it_clear", "label": "IT Cleared", "done": bool(er.it_cleared_at), "ts": er.it_cleared_at},
        {"key": "mgr_final", "label": "Manager Final OK", "done": bool(er.final_ok_by_manager_at), "ts": er.final_ok_by_manager_at},
        {"key": "hr_final", "label": "HR Finalised", "done": er.status == "final_approved", "ts": er.final_ok_by_hr_at},
    ])

    current_idx = next((i for i, s in enumerate(steps) if not s["done"]), len(steps) - 1)
    fnf_ready = False
    fnf_message = None

    if er.status == "final_approved":
        fnf_ready, fnf_message = is_fnf_ready(er)

    return render_template(
        "exit/view.html",
        er=er,
        docs=docs,
        employees=employees,
        steps=steps,
        current_idx=current_idx,
        fnf_ready=fnf_ready,
        fnf_message=fnf_message,
        asset_allocations=asset_allocations,
        has_assets=has_assets,
        asset_handover_done=asset_handover_done
    )
# -----------------------------
# Manager/HOD approval of resignation
# -----------------------------
@exit_bp.post("/<int:exit_id>/manager-approve")
@login_required
def manager_approve(exit_id):
    if not require_manager():
        flash("Only Manager/HOD can approve.", "danger")
        return redirect(url_for("exit.view", exit_id=exit_id))

    er = ExitRequest.query.get_or_404(exit_id)

    # NEW: fetch the employee and move to notice
    emp = Employee.query.get_or_404(er.employee_id)

    er.manager_approved_at = datetime.utcnow()
    er.manager_approved_by = current_user.id
    er.manager_accepted = True  # (you already use this flag for payroll logic)
    er.status = "manager_approved"

    # NEW: put employee into notice period (do not set exit_date here)
    if emp.status != "resigned":
        emp.status = "notice"

    db.session.add(ExitEvent(exit_id=er.id, actor_id=current_user.id, action="manager_approved"))
    db.session.add(emp)  # make sure the status change is persisted
    db.session.commit()

    flash("Resignation accepted by Manager. Employee moved to notice period.", "success")
    return redirect(url_for("exit.view", exit_id=exit_id))
# (Optional) HOD approve — include only if you enforce HOD in your org
@exit_bp.post("/<int:exit_id>/hod-approve")
@login_required
def hod_approve(exit_id):
    if not require_manager():
        flash("Only HOD can approve.", "danger")
        return redirect(url_for("exit.view", exit_id=exit_id))

    er = ExitRequest.query.get_or_404(exit_id)
    er.hod_approved_at = datetime.utcnow()
    er.hod_approved_by = current_user.id

    db.session.add(ExitEvent(exit_id=er.id, actor_id=current_user.id, action="hod_approved"))
    db.session.commit()
    flash("HOD approved.", "success")
    return redirect(url_for("exit.view", exit_id=exit_id))

# -----------------------------
# Employee: upload handover + assign viewers
# -----------------------------
@exit_bp.post("/<int:exit_id>/handover/upload")
@login_required
def upload_handover(exit_id):
    er = ExitRequest.query.get_or_404(exit_id)

    f = request.files.get("file")
    title = request.form.get("title") or "Handover"
    viewer_ids = request.form.getlist("viewer_ids")  # list of employee ids (strings)

    rel_path = save_exit_file(f, exit_id)
    if not rel_path:
        flash("Please choose a file to upload.", "warning")
        return redirect(url_for("exit.view", exit_id=exit_id))

    doc = HandoverDoc(exit_id=exit_id, title=title, file_path=rel_path, uploaded_by=current_user.id)
    db.session.add(doc)
    db.session.flush()

    for vid in viewer_ids:
        try:
            db.session.add(HandoverDocViewer(doc_id=doc.id, viewer_emp_id=int(vid)))
        except Exception:
            continue

    # Move status forward if relevant
    er.status = "handover_in_progress"
    db.session.add(ExitEvent(exit_id=er.id, actor_id=current_user.id, action="uploaded_doc", note=title))
    db.session.commit()

    flash("Handover document uploaded and viewers assigned.", "success")
    return redirect(url_for("exit.view", exit_id=exit_id))

@exit_bp.post("/asset-handover/<int:allocation_id>")
@login_required
def asset_handover(allocation_id):
    if not require_it():
        flash("Only IT/Admin can update asset handover.", "danger")
        return redirect(request.referrer or url_for("employee.dashboard"))

    allocation = AssetAllocation.query.get_or_404(allocation_id)

    er = ExitRequest.query.filter_by(employee_id=allocation.employee_id).order_by(ExitRequest.id.desc()).first()
    if not er:
        flash("No exit request found for this employee.", "warning")
        return redirect(request.referrer or url_for("employee.dashboard"))

    allocation.status = "returned"
    allocation.actual_return_date = datetime.utcnow().date()
    allocation.condition_on_return = request.form.get("condition_on_return") or allocation.condition_on_return
    allocation.remarks = request.form.get("remarks") or allocation.remarks

    db.session.add(ExitEvent(
        exit_id=er.id,
        actor_id=current_user.id,
        action="asset_handover",
        note=f"Asset allocation #{allocation.id} marked returned"
    ))
    db.session.commit()

    flash("Asset handover updated successfully.", "success")
    return redirect(url_for("exit.view", exit_id=er.id))
# -----------------------------
# Viewer: acknowledge handover
# -----------------------------
# routes/exit.py
@exit_bp.post("/handover/<int:doc_id>/ack")
@login_required
def ack_handover(doc_id):
    v = (HandoverDocViewer.query
         .filter_by(doc_id=doc_id, viewer_emp_id=current_user.id, can_view=True)  # <-- use current_user.id
         .first_or_404())
    v.acknowledged_at = datetime.utcnow()
    db.session.commit()

    # (optional) add an ExitEvent
    d = HandoverDoc.query.get(doc_id)
    if d:
        db.session.add(ExitEvent(exit_id=d.exit_id, actor_id=current_user.id, action="handover_ack"))
        db.session.commit()

    flash("✅ Handover acknowledged.", "success")
    return redirect(request.referrer or url_for("exit.list_my_acks"))

# -----------------------------
# IT clearance
# -----------------------------
@exit_bp.post("/<int:exit_id>/it-clear")
@login_required
def it_clear(exit_id):
    if not require_it():
        flash("Only IT can mark clearance.", "danger")
        return redirect(url_for("exit.view", exit_id=exit_id))

    er = ExitRequest.query.get_or_404(exit_id)
    er.it_cleared_at = datetime.utcnow()
    er.it_clearance_by = current_user.id

    maybe_move_to_clearances(er)
    db.session.add(ExitEvent(exit_id=exit_id, actor_id=current_user.id, action="it_cleared"))
    db.session.commit()

    flash("IT clearance recorded.", "success")
    return redirect(url_for("exit.view", exit_id=exit_id))

# -----------------------------
# Manager final OK (post-handover)
# -----------------------------
@exit_bp.post("/<int:exit_id>/manager-final-ok")
@login_required
def manager_final_ok(exit_id):
    if not require_manager():
        flash("Only Manager/HOD can give final OK.", "danger")
        return redirect(url_for("exit.view", exit_id=exit_id))

    er = ExitRequest.query.get_or_404(exit_id)
    er.final_ok_by_manager_at = datetime.utcnow()

    maybe_move_to_clearances(er)
    db.session.add(ExitEvent(exit_id=exit_id, actor_id=current_user.id, action="manager_final_ok"))
    db.session.commit()

    flash("Manager final OK recorded.", "success")
    return redirect(url_for("exit.view", exit_id=exit_id))

# -----------------------------
# HR final approval (closes exit if all prerequisites satisfied)
# -----------------------------
@exit_bp.post("/<int:exit_id>/final-approve")
@login_required
def final_approve(exit_id):
    if not require_hr():
        flash("Only HR can finalise exit.", "danger")
        return redirect(url_for("exit.view", exit_id=exit_id))

    er = ExitRequest.query.get_or_404(exit_id)

    if not all_clear(er.id):
        flash("All clearances & acknowledgments are not complete yet.", "warning")
        return redirect(url_for("exit.view", exit_id=exit_id))

    # --- NEW: update employee record on HR finalise ---
    emp = Employee.query.get_or_404(er.employee_id)
    emp.status = "resigned"
    emp.exit_date = er.proposed_lwd  # if you store a revised LWD later, use that instead

    er.final_ok_by_hr_at = datetime.utcnow()
    er.status = "final_approved"

    db.session.add(ExitEvent(exit_id=exit_id, actor_id=current_user.id, action="final_approved"))
    db.session.add(emp)  # ensure status/exit_date change is persisted
    
    db.session.commit()

    flash("Exit finalised. Employee marked as 'resigned' with exit date set.", "success")
    return redirect(url_for("exit.view", exit_id=exit_id))
# -----------------------------
# My Acks (for viewers)
# -----------------------------
@exit_bp.get("/my-acks")
@login_required
def list_my_acks():
    my_emp_id = getattr(current_user, "employee_id", None)
    if not my_emp_id:
        flash("No employee mapping found for your user.", "warning")
        return redirect(url_for("exit.create_exit"))

    # (doc, exit, viewer) tuples
    rows = (
        db.session.query(HandoverDoc, ExitRequest, HandoverDocViewer)
        .join(ExitRequest, ExitRequest.id == HandoverDoc.exit_id)
        .join(HandoverDocViewer, HandoverDocViewer.doc_id == HandoverDoc.id)
        .filter(HandoverDocViewer.viewer_emp_id == my_emp_id)
        .order_by(HandoverDoc.uploaded_at.desc())
        .all()
    )

    data = [{"doc": d, "exit": er, "viewer": v} for (d, er, v) in rows]
    return render_template("exit/my_acks.html", rows=data)

# -----------------------------
# Helpers
# -----------------------------
def all_clear(exit_id: int) -> bool:
    er = ExitRequest.query.get(exit_id)
    if not er:
        return False

    if not (er.manager_approved_at and er.it_cleared_at and er.final_ok_by_manager_at):
        return False

    docs = HandoverDoc.query.filter_by(exit_id=exit_id).all()
    for d in docs:
        pending = HandoverDocViewer.query.filter_by(
            doc_id=d.id, can_view=True, acknowledged_at=None
        ).count()
        if pending > 0:
            return False

    if not is_asset_handover_complete(er.employee_id):
        return False

    return True

def maybe_move_to_clearances(er: ExitRequest):
    """
    Move to 'clearances_pending' once manager approved and any handover exists.
    """
    has_docs = db.session.query(HandoverDoc.id).filter_by(exit_id=er.id).first() is not None
    if er.manager_approved_at and has_docs:
        er.status = "clearances_pending"

@exit_bp.route('/gratuity/settings', methods=['GET', 'POST'])
@login_required
def gratuity_settings():
    setting = GratuitySetting.query.first()

    if request.method == 'POST':
        is_enabled = True if request.form.get('is_enabled') == 'on' else False
        is_covered = True if request.form.get('is_covered') == 'on' else False
        min_years = request.form.get('min_service_years') or 5
        round_up = request.form.get('round_up_after_months') or 6
        max_limit = request.form.get('max_limit') or 2000000
        waive = True if request.form.get('waive') == 'on' else False
        formula = request.form.get('formula') or '(last_drawn_salary * 15 * eligible_service_years) / 26'
        selected_heads = request.form.getlist('components')

        if not setting:
            setting = GratuitySetting()
            db.session.add(setting)

        setting.is_enabled = is_enabled
        setting.is_covered_under_act = is_covered
        setting.min_service_years = int(min_years)
        setting.round_up_after_months = int(round_up)
        setting.max_limit = float(max_limit)
        setting.waive_service_for_death_disability = waive
        setting.formula = formula

        # Remove old components
        GratuityComponent.query.filter_by(setting_id=setting.id).delete()

        # Add new selected components
        for head_id in selected_heads:
            head = EarningHead.query.get(int(head_id))
            if head:
                comp = GratuityComponent(
                    setting_id=setting.id,
                    head_id=head.id,
                    head_name=head.name
                )
                db.session.add(comp)

        db.session.commit()
        flash("Gratuity settings saved successfully.", "success")
        return redirect(url_for('exit.gratuity_settings'))

    earning_heads = EarningHead.query.all()
    selected_ids = []

    if setting:
        selected_ids = [c.head_id for c in setting.components]

    return render_template(
        'exit/gratuity_settings.html',
        setting=setting,
        earning_heads=earning_heads,
        selected_ids=selected_ids
    )

@exit_bp.route('/gratuity/list')
@login_required
def gratuity_list():
    setting = GratuitySetting.query.first()

    if not setting or not setting.is_enabled:
        flash("Gratuity module is disabled.", "warning")
        return redirect(url_for('employee.dashboard'))

    component_ids = [
        c.head_id for c in GratuityComponent.query.filter_by(setting_id=setting.id).all()
    ]

    exit_requests = ExitRequest.query.order_by(ExitRequest.proposed_lwd.desc()).all()

    results = []

    for ex in exit_requests:
        emp = ex.employee
        if not emp:
            continue

        doj = emp.date_of_joining
        exit_date = ex.proposed_lwd

        if not doj or not exit_date:
            continue

        service_days = (exit_date - doj).days
        if service_days < 0:
            continue

        service_years = service_days // 365
        remaining_months = (service_days % 365) // 30

        eligible_service_years = service_years
        if remaining_months > (setting.round_up_after_months or 6):
            eligible_service_years += 1

        eligible = eligible_service_years >= (setting.min_service_years or 5)

        last_salary = 0.0
        component_breakup = []

        if component_ids:
            salary_rows = EmployeeSalaryStructures.query.filter(
                EmployeeSalaryStructures.employee_id == emp.id,
                EmployeeSalaryStructures.type == 'earning',
                EmployeeSalaryStructures.head_id.in_(component_ids)
            ).all()
        else:
            salary_rows = []

        for row in salary_rows:
            amt = float(row.amount or 0)

            head = EarningHead.query.get(row.head_id)
            head_name = head.name if head else f"Head {row.head_id}"

            last_salary += amt
            component_breakup.append(f"{head_name}: {amt}")

        gratuity = 0.0
        formula_error = None
        formula_text = setting.formula or '(last_drawn_salary * 15 * eligible_service_years) / 26'

        if eligible and last_salary > 0:
            gratuity, formula_error = evaluate_gratuity_formula(
                formula=formula_text,
                last_drawn_salary=last_salary,
                eligible_service_years=eligible_service_years,
                service_years=service_years,
                remaining_months=remaining_months
            )

            if setting.max_limit:
                gratuity = min(gratuity, float(setting.max_limit))

        # SAVE / UPDATE GRATUITY CALCULATION
        calc = GratuityCalculation.query.filter_by(
            employee_id=emp.id,
            exit_date=exit_date
        ).first()

        if not calc:
            calc = GratuityCalculation(
                employee_id=emp.id,
                employee_code=emp.employee_code,
                employee_name=emp.full_name,
                date_of_joining=doj,
                exit_date=exit_date,
                created_by=current_user.id if current_user.is_authenticated else None
            )
            db.session.add(calc)

        calc.last_drawn_salary = round(last_salary, 2)
        calc.service_years = service_years
        calc.service_months = remaining_months
        calc.eligible_service_years = eligible_service_years
        calc.is_eligible = eligible
        calc.eligibility_reason = 'Eligible' if eligible else f'Less than {setting.min_service_years} years service'
        calc.calculated_amount = round(gratuity, 2)
        calc.capped_amount = round(gratuity, 2)
        calc.status = 'approved' if eligible and gratuity > 0 else 'draft'
        calc.exit_reason = ex.status

        results.append({
            'exit_request': ex,
            'employee': emp,
            'doj': doj,
            'exit_date': exit_date,
            'service_years': service_years,
            'remaining_months': remaining_months,
            'eligible_service_years': eligible_service_years,
            'last_salary': round(last_salary, 2),
            'gratuity': round(gratuity, 2),
            'eligible': eligible,
            'component_breakup': ", ".join(component_breakup),
            'formula_used': formula_text,
            'formula_error': formula_error
        })

    db.session.commit()
    return render_template('exit/gratuity_list.html', results=results)

#-----------------------------
# F&F Helpers
#-----------------------------

def calculate_fnf_net(s):
    return round(
        (s.salary_amount or 0)
        - (s.deductions_amount or 0)
        - (s.penalty_recovery or 0)
        - (s.loan_advance_recovery or 0)
        - (s.asset_recovery or 0)
        - (s.handover_recovery or 0)
        + (s.bonus_amount or 0)
        + (s.gratuity_amount or 0)
        + (s.other_benefits or 0),
        2
    )

def create_fnf_after_exit(er):
    fnf = FullFinalSettlement.query.filter_by(exit_id=er.id).first()

    emp_id = er.employee_id
    salary = 0
    deductions = 0

    if er.proposed_lwd:
        month = er.proposed_lwd.strftime('%Y-%m')

        payroll = Payroll.query.filter_by(employee_id=emp_id, month=month).first()
        if payroll:
            salary = float(payroll.gross_pay or 0)

        deductions = db.session.query(db.func.sum(FrozenSalaryComponent.amount)).filter(
            FrozenSalaryComponent.employee_id == emp_id,
            FrozenSalaryComponent.month == month,
            FrozenSalaryComponent.component_type == 'deduction'
        ).scalar() or 0

    bonus = sum([
        float(x.final_amount or 0)
        for x in BonusEntry.query.join(BonusRun).filter(
            BonusEntry.employee_id == emp_id,
            BonusRun.status.in_(['frozen', 'posted'])
        ).all()
    ])

    gratuity_calc = GratuityCalculation.query.filter_by(employee_id=emp_id).order_by(GratuityCalculation.id.desc()).first()
    gratuity = float(gratuity_calc.capped_amount or 0) if gratuity_calc else 0

    if not fnf:
        fnf = FullFinalSettlement(
            exit_id=er.id,
            employee_id=emp_id,
            created_by=current_user.id
        )
        db.session.add(fnf)

    fnf.salary_amount = salary
    fnf.deductions_amount = deductions
    fnf.bonus_amount = bonus
    fnf.gratuity_amount = gratuity
    fnf.net_payable = calculate_fnf_net(fnf)

    db.session.flush()

    if gratuity_calc:
        gd = GratuityDisbursement.query.filter_by(gratuity_calc_id=gratuity_calc.id).first()
        if not gd:
            gd = GratuityDisbursement(
                gratuity_calc_id=gratuity_calc.id,
                exit_id=er.id,
                employee_id=emp_id,
                amount=gratuity
            )
            db.session.add(gd)
        else:
            gd.amount = gratuity

    return fnf

@exit_bp.route('/<int:exit_id>/fnf')
@login_required
def view_fnf(exit_id):
    er = ExitRequest.query.get_or_404(exit_id)
    if er.status != 'final_approved':
        flash("F&F is available only after HR final approval.", "warning")
        return redirect(url_for('exit.view', exit_id=exit_id))

    ready, msg = is_fnf_ready(er)
    if not ready:
        flash(msg, "warning")
        return redirect(url_for('exit.view', exit_id=exit_id))

    fnf = create_fnf_after_exit(er)
    db.session.commit()
    return render_template('exit/fnf_sheet.html', er=er, fnf=fnf)

@exit_bp.post('/<int:exit_id>/fnf/save')
@login_required
def save_fnf(exit_id):
    fnf = FullFinalSettlement.query.filter_by(exit_id=exit_id).first_or_404()
    if fnf.status == 'paid':
        flash("Paid F&F cannot be edited.", "danger")
        return redirect(url_for('exit.view_fnf', exit_id=exit_id))

    fnf.penalty_recovery = float(request.form.get('penalty_recovery') or 0)
    fnf.loan_advance_recovery = float(request.form.get('loan_advance_recovery') or 0)
    fnf.asset_recovery = float(request.form.get('asset_recovery') or 0)
    fnf.handover_recovery = float(request.form.get('handover_recovery') or 0)
    fnf.other_benefits = float(request.form.get('other_benefits') or 0)

    fnf.net_payable = calculate_fnf_net(fnf)

    if fnf.status == 'approved':
        fnf.status = 'draft'

    db.session.commit()
    flash("F&F updated", "success")

    return redirect(url_for('exit.view_fnf', exit_id=exit_id))

@exit_bp.route('/gratuity/disbursement')
@login_required
def gratuity_disbursement():
    rows = GratuityDisbursement.query.all()
    return render_template('exit/gratuity_disbursement.html', rows=rows)

@exit_bp.post('/gratuity/<int:id>/paid')
@login_required
def gratuity_paid(id):
    g = GratuityDisbursement.query.get_or_404(id)
    g.payment_status = 'paid'
    g.payment_date = datetime.utcnow().date()
    g.payment_reference = request.form.get('ref')

    calc = GratuityCalculation.query.get(g.gratuity_calc_id)
    if calc:
        calc.status = 'paid'

    db.session.commit()
    flash("Gratuity marked as paid.", "success")
    return redirect(url_for('exit.gratuity_disbursement'))


@exit_bp.post('/gratuity/<int:id>/pending')
@login_required
def gratuity_pending(id):
    g = GratuityDisbursement.query.get_or_404(id)
    g.payment_status = 'pending'
    g.payment_date = None
    g.payment_reference = None

    calc = GratuityCalculation.query.get(g.gratuity_calc_id)
    if calc and calc.status == 'paid':
        calc.status = 'approved'

    db.session.commit()
    flash("Gratuity moved back to pending.", "warning")
    return redirect(url_for('exit.gratuity_disbursement'))

@exit_bp.route('/fnf/list')
@login_required
def fnf_list():
    rows = FullFinalSettlement.query.order_by(FullFinalSettlement.id.desc()).all()
    return render_template('exit/fnf_list.html', rows=rows)

def is_fnf_ready(exit_request):
    if not exit_request.proposed_lwd:
        return False, "Exit date is not available."

    target_month = exit_request.proposed_lwd.strftime('%Y-%m')

    payroll = Payroll.query.filter_by(
        employee_id=exit_request.employee_id,
        month=target_month
    ).first()

    if not payroll:
        return False, f"Payroll not processed for {target_month}."

    frozen_components = FrozenSalaryComponent.query.filter_by(
        employee_id=exit_request.employee_id,
        month=target_month
    ).count()

    if frozen_components == 0:
        return False, f"Payroll not frozen for {target_month}."

    return True, None

@exit_bp.post('/<int:exit_id>/fnf/approve')
@login_required
def approve_fnf(exit_id):
    fnf = FullFinalSettlement.query.filter_by(exit_id=exit_id).first_or_404()
    if fnf.status == 'paid':
        flash("Paid F&F cannot be approved again.", "warning")
        return redirect(url_for('exit.view_fnf', exit_id=exit_id))
    fnf.status = 'approved'
    db.session.commit()
    flash("F&F approved successfully.", "success")
    return redirect(url_for('exit.view_fnf', exit_id=exit_id))


@exit_bp.post('/<int:exit_id>/fnf/mark-paid')
@login_required
def mark_fnf_paid(exit_id):
    fnf = FullFinalSettlement.query.filter_by(exit_id=exit_id).first_or_404()
    fnf.status = 'paid'
    db.session.commit()
    flash("F&F marked as paid.", "success")
    return redirect(url_for('exit.view_fnf', exit_id=exit_id))

@exit_bp.route('/fnf/list/download')
@login_required
def download_fnf_list():
    rows = FullFinalSettlement.query.order_by(FullFinalSettlement.id.desc()).all()

    data = []
    for row in rows:
        data.append({
            "Employee Name": row.employee.full_name if row.employee else "",
            "Employee Code": row.employee.employee_code if row.employee else "",
            "Exit ID": row.exit_id,
            "Salary": float(row.salary_amount or 0),
            "Deductions": float(row.deductions_amount or 0),
            "Penalty Recovery": float(row.penalty_recovery or 0),
            "Loan & Advance Recovery": float(row.loan_advance_recovery or 0),
            "Asset Recovery": float(row.asset_recovery or 0),
            "Handover Recovery": float(row.handover_recovery or 0),
            "Bonus": float(row.bonus_amount or 0),
            "Gratuity": float(row.gratuity_amount or 0),
            "Other Benefits": float(row.other_benefits or 0),
            "Net Payable": float(row.net_payable or 0),
            "Status": row.status or "",
            "Remarks": row.remarks or ""
        })

    df = pd.DataFrame(data)

    output = io.BytesIO()
    with pd.ExcelWriter(output, engine='openpyxl') as writer:
        df.to_excel(writer, index=False, sheet_name='FNF List')
    output.seek(0)

    return send_file(
        output,
        as_attachment=True,
        download_name='full_final_settlements.xlsx',
        mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    )

@exit_bp.route('/<int:exit_id>/fnf/download')
@login_required
def download_fnf_detail(exit_id):
    er = ExitRequest.query.get_or_404(exit_id)
    fnf = FullFinalSettlement.query.filter_by(exit_id=exit_id).first_or_404()

    data = [
        ["Employee Name", er.employee.full_name if er.employee else ""],
        ["Employee Code", er.employee.employee_code if er.employee else ""],
        ["Exit ID", er.id],
        ["Proposed LWD", er.proposed_lwd.strftime('%d-%m-%Y') if er.proposed_lwd else ""],
        ["Salary", float(fnf.salary_amount or 0)],
        ["Deductions", float(fnf.deductions_amount or 0)],
        ["Penalty Recovery", float(fnf.penalty_recovery or 0)],
        ["Loan & Advance Recovery", float(fnf.loan_advance_recovery or 0)],
        ["Asset Recovery", float(fnf.asset_recovery or 0)],
        ["Handover Recovery", float(fnf.handover_recovery or 0)],
        ["Bonus", float(fnf.bonus_amount or 0)],
        ["Gratuity", float(fnf.gratuity_amount or 0)],
        ["Other Benefits", float(fnf.other_benefits or 0)],
        ["Net Payable", float(fnf.net_payable or 0)],
        ["Status", fnf.status or ""],
        ["Remarks", fnf.remarks or ""]
    ]

    df = pd.DataFrame(data, columns=["Particular", "Value"])

    output = io.BytesIO()
    with pd.ExcelWriter(output, engine='openpyxl') as writer:
        df.to_excel(writer, index=False, sheet_name='FNF Detail')
    output.seek(0)

    emp_code = er.employee.employee_code if er.employee else f"exit_{er.id}"

    return send_file(
        output,
        as_attachment=True,
        download_name=f'fnf_{emp_code}.xlsx',
        mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    )