"""HR & Payroll screens: employees, leave, loans, payroll runs, final settlements, reports and settings."""
import csv
import io
from datetime import date

from flask import Blueprint, Response, abort, flash, redirect, render_template, request, url_for

from .. import admin_required, check_branch, context_branch_ids, login_required, module_required, write_required
from ..attachments import remove_all as remove_attachments
from ..costcentres import options as cost_centre_options, parse as parse_cost_centre
from ..db import commit, get_db, placeholders, q, x
from ..i18n import nm, t
from ..ledger import AccountingError, audit, now
from ..money import to_int
from ..payroll import EOSB_BASES, LAWS, LEAVE_KINDS, REASONS, compute_settlement, create_run, calculate_run, \
    disburse_loan, eosb, expiring_documents, leave_balance, leave_days, leave_daily, loan_balance, loan_status, \
    monthly_wage, pay, post_run, post_settlement, run_totals, service_years, settings
from ..migrations import HR_ACCOUNTS
from ..schedules import branch_decimals
from ..stock import next_number
from .common import accounts_list, branch_map, default_branch, journals_for, user_id

bp = Blueprint("hr", __name__, url_prefix="/hr")
bp.before_request(module_required("hr"))

MONEY = ("basic", "housing", "transport", "other_allowance")
TEXT = ("code", "name_en", "name_ar", "department", "job_title", "nationality", "gender", "birth_date", "join_date",
        "id_number", "id_expiry", "passport_no", "passport_expiry", "gosi_no", "bank_name", "iban", "email", "phone",
        "notes")


def get_employee(eid):
    e = q("SELECT * FROM employees WHERE id = ?", (eid,), one=True)
    if not e:
        abort(404)
    check_branch(e["branch_id"])
    return e


def employees_in_scope(active_only=False):
    ids = context_branch_ids()
    if not ids:
        return []
    sql = f"SELECT * FROM employees WHERE branch_id IN ({placeholders(ids)})"
    if active_only:
        sql += " AND state = 'active'"
    return q(sql + " ORDER BY code", list(ids))


def currency_of(branch_id):
    return q("SELECT currency FROM branches WHERE id = ?", (branch_id,), one=True)["currency"]


@bp.route("/")
@login_required
def index():
    return redirect(url_for("hr.employees"))


# ---------------------------------------------------------------- employees

@bp.route("/employees")
@login_required
def employees():
    state = request.args.get("state", "active")
    term = request.args.get("q", "").strip().lower()
    rows = [e for e in employees_in_scope() if (state == "all" or e["state"] == state)
            and (not term or term in f"{e['code']} {e['name_en']} {e['name_ar'] or ''} {e['department'] or ''} "
                                      f"{e['job_title'] or ''}".lower())]
    return render_template("hr/employees.html", rows=rows, state=state, term=term, branches=branch_map(),
                           wage=monthly_wage, laws=LAWS)


@bp.route("/employees/new", methods=["GET", "POST"])
@bp.route("/employees/<int:eid>/edit", methods=["GET", "POST"])
@write_required
def employee_form(eid=None):
    e = get_employee(eid) if eid else None
    if request.method == "POST":
        f = request.form
        try:
            vals = {k: (f.get(k) or "").strip() or None for k in TEXT}
            vals.update({k: to_int(f.get(k)) for k in MONEY})
            vals.update(branch_id=check_branch(f["branch_id"]), cost_centre_id=parse_cost_centre(f.get("cost_centre_id")),
                        law=f.get("law") if f.get("law") in LAWS else "ksa", is_national=1 if f.get("is_national") else 0,
                        annual_leave_days=int(f.get("annual_leave_days") or 0),
                        leave_opening=float(f.get("leave_opening") or 0))
            if not vals["code"] or not vals["name_en"]:
                raise AccountingError(t("Employee number and name are required."))
            if not vals["join_date"]:
                raise AccountingError(t("Joining date is required."))
            if min(vals[k] for k in MONEY) < 0:
                raise AccountingError(t("Debit and credit cannot be negative."))
            dup = q("SELECT id FROM employees WHERE code = ? AND id != ?", (vals["code"], eid or -1), one=True)
            if dup:
                raise AccountingError(t("This employee number is already used."))
            if e and e["branch_id"] != vals["branch_id"] and (e["eosb_booked"] or e["leave_booked"]):
                raise AccountingError(t("Provisions are booked in the employee's branch; settle the employee there "
                                        "and set up a new record in the other branch."))
            if e:
                x(f"UPDATE employees SET {', '.join(k + ' = ?' for k in vals)} WHERE id = ?", list(vals.values()) + [eid])
            else:
                vals["created_at"] = now()
                eid = x(f"INSERT INTO employees ({', '.join(vals)}) VALUES ({', '.join('?' * len(vals))})",
                        list(vals.values()))
            audit(user_id(), "save", "employee", eid, vals["code"])
            commit()
            flash(t("Saved."), "success")
            return redirect(url_for("hr.employee", eid=eid))
        except (AccountingError, ValueError, KeyError) as ex:
            get_db().rollback()
            flash(str(ex), "error")
            e = dict(request.form.items())
            for k in MONEY:
                e[k] = to_int(e.get(k)) if str(e.get(k) or "").replace(",", "").replace(".", "").isdigit() else 0
    elif not e:
        bid = default_branch()
        last = q("SELECT code FROM employees ORDER BY id DESC LIMIT 1", one=True)
        nxt = ""
        if last and last["code"][-3:].isdigit():
            nxt = last["code"][:-3] + f"{int(last['code'][-3:]) + 1:03d}"
        e = {"branch_id": bid, "code": nxt or "E001", "join_date": date.today().isoformat(),
             "law": "uae" if bid and currency_of(bid) == "AED" else "ksa"}
    return render_template("hr/employee_form.html", e=e, branches=branch_map(), laws=LAWS,
                           cost_centres=cost_centre_options(dict(e).get("cost_centre_id")))


@bp.route("/employees/<int:eid>")
@login_required
def employee(eid):
    e = get_employee(eid)
    today = date.today().isoformat()
    end = e["leave_date"] or today
    years, wage, full, _ = eosb(e, end, "termination")
    resign = eosb(e, end, "resignation")[3]
    bal = leave_balance(e, end)
    status = loan_status(eid)
    loans = [dict(ln, repaid=status.get(ln["id"], (0, 0))[0], balance=status.get(ln["id"], (0, ln["amount"]))[1])
             for ln in q("SELECT * FROM employee_loans WHERE employee_id = ? ORDER BY date DESC", (eid,))]
    leaves = q("SELECT * FROM leave_requests WHERE employee_id = ? ORDER BY date_from DESC", (eid,))
    slips = q("SELECT l.*, r.period, r.number, r.state AS run_state FROM payroll_lines l "
              "JOIN payroll_runs r ON r.id = l.run_id WHERE l.employee_id = ? ORDER BY r.period DESC", (eid,))
    settlement = q("SELECT * FROM eos_settlements WHERE employee_id = ? ORDER BY id DESC LIMIT 1", (eid,), one=True)
    return render_template("hr/employee.html", e=e, cur=currency_of(e["branch_id"]), years=years, wage=wage,
                           eosb_full=full, eosb_resign=resign, leave_bal=bal, leave_value=max(bal, 0) * leave_daily(e),
                           loans=loans, leaves=leaves, slips=slips, settlement=settlement, monthly=monthly_wage(e),
                           laws=LAWS, kinds=LEAVE_KINDS, branches=branch_map(), today=today)


@bp.route("/employees/<int:eid>/delete", methods=["POST"])
@write_required
def employee_delete(eid):
    e = get_employee(eid)
    used = any(q(f"SELECT 1 FROM {tbl} WHERE employee_id = ? LIMIT 1", (eid,), one=True)
               for tbl in ("payroll_lines", "employee_loans", "eos_settlements", "leave_requests"))
    if used:
        flash(t("This employee has payroll, leave or loan records and cannot be deleted. Use a final settlement."),
              "error")
        return redirect(url_for("hr.employee", eid=eid))
    x("DELETE FROM employees WHERE id = ?", (eid,))
    remove_attachments("employee", eid)
    audit(user_id(), "delete", "employee", eid, e["code"])
    commit()
    flash(t("Deleted."), "success")
    return redirect(url_for("hr.employees"))


# ---------------------------------------------------------------- leave

def get_leave(lid):
    lv = q("SELECT l.*, e.code, e.name_en, e.name_ar, e.branch_id FROM leave_requests l "
           "JOIN employees e ON e.id = l.employee_id WHERE l.id = ?", (lid,), one=True)
    if not lv:
        abort(404)
    check_branch(lv["branch_id"])
    return lv


@bp.route("/leave")
@login_required
def leave():
    ids = context_branch_ids()
    state = request.args.get("state", "")
    rows = []
    if ids:
        sql = (f"SELECT l.*, e.code, e.name_en, e.name_ar FROM leave_requests l JOIN employees e ON e.id = l.employee_id "
               f"WHERE e.branch_id IN ({placeholders(ids)})")
        args = list(ids)
        if state:
            sql += " AND l.state = ?"
            args.append(state)
        rows = q(sql + " ORDER BY l.date_from DESC, l.id DESC", args)
    return render_template("hr/leave.html", rows=rows, state=state, kinds=LEAVE_KINDS)


@bp.route("/leave/new", methods=["GET", "POST"])
@bp.route("/leave/<int:lid>/edit", methods=["GET", "POST"])
@write_required
def leave_form(lid=None):
    lv = get_leave(lid) if lid else None
    if lv and lv["state"] != "draft":
        flash(t("Only draft documents can be edited."), "error")
        return redirect(url_for("hr.leave"))
    if request.method == "POST":
        f = request.form
        try:
            emp = get_employee(int(f["employee_id"]))
            vals = {"employee_id": emp["id"], "kind": f["kind"] if f.get("kind") in LEAVE_KINDS else "annual",
                    "date_from": f["date_from"], "date_to": f["date_to"], "notes": f.get("notes")}
            if vals["date_to"] < vals["date_from"]:
                raise AccountingError(t("The end date is before the start date."))
            vals["days"] = float(f.get("days") or leave_days(vals["date_from"], vals["date_to"]))
            if lv:
                x(f"UPDATE leave_requests SET {', '.join(k + ' = ?' for k in vals)} WHERE id = ?",
                  list(vals.values()) + [lid])
            else:
                vals.update(number=next_number("leave_requests", "number", "LV", emp["branch_id"], vals["date_from"]),
                            state="draft", created_by=user_id(), created_at=now())
                lid = x(f"INSERT INTO leave_requests ({', '.join(vals)}) VALUES ({', '.join('?' * len(vals))})",
                        list(vals.values()))
            if f.get("action") == "approve":
                approve_leave(lid)
            audit(user_id(), "save", "leave", lid)
            commit()
            flash(t("Saved."), "success")
            return redirect(url_for("hr.leave"))
        except (AccountingError, ValueError, KeyError) as ex:
            get_db().rollback()
            flash(str(ex), "error")
            lv = dict(request.form.items())
    elif not lv:
        lv = {"employee_id": request.args.get("employee", type=int), "kind": "annual",
              "date_from": date.today().isoformat(), "date_to": date.today().isoformat()}
    emps = employees_in_scope(active_only=True)
    balances = {e["id"]: leave_balance(e, date.today()) for e in emps}
    return render_template("hr/leave_form.html", lv=lv, employees=emps, kinds=LEAVE_KINDS, balances=balances)


def approve_leave(lid):
    lv = get_leave(lid)
    if lv["state"] != "draft":
        raise AccountingError(t("This action is not allowed in the current state."))
    if lv["kind"] == "annual":
        e = get_employee(lv["employee_id"])
        bal = leave_balance(e, lv["date_from"])
        if lv["days"] > bal + 0.001:
            flash(f"{t('Annual leave balance is only')} {bal:.1f} {t('days')}; {t('the balance goes negative.')}", "warning")
    x("UPDATE leave_requests SET state = 'approved', approved_by = ? WHERE id = ?", (user_id(), lid))


@bp.route("/leave/<int:lid>/<action>", methods=["POST"])
@write_required
def leave_action(lid, action):
    lv = get_leave(lid)
    try:
        if action == "approve":
            approve_leave(lid)
        elif action == "cancel" and lv["state"] in ("draft", "approved"):
            x("UPDATE leave_requests SET state = 'cancelled' WHERE id = ?", (lid,))
        elif action == "delete" and lv["state"] in ("draft", "cancelled"):
            x("DELETE FROM leave_requests WHERE id = ?", (lid,))
        else:
            raise AccountingError(t("This action is not allowed in the current state."))
        audit(user_id(), action, "leave", lid, lv["number"])
        commit()
        flash(t("Done."), "success")
    except AccountingError as ex:
        get_db().rollback()
        flash(str(ex), "error")
    return redirect(request.referrer or url_for("hr.leave"))


# ---------------------------------------------------------------- loans

def get_loan(lid):
    ln = q("SELECT l.*, e.code, e.name_en, e.name_ar FROM employee_loans l JOIN employees e ON e.id = l.employee_id "
           "WHERE l.id = ?", (lid,), one=True)
    if not ln:
        abort(404)
    check_branch(ln["branch_id"])
    return ln


@bp.route("/loans")
@login_required
def loans():
    ids = context_branch_ids()
    rows = []
    if ids:
        rows = q(f"SELECT l.*, e.code, e.name_en, e.name_ar FROM employee_loans l JOIN employees e ON e.id = l.employee_id "
                 f"WHERE l.branch_id IN ({placeholders(ids)}) ORDER BY l.date DESC, l.id DESC", list(ids))
    status = {}
    for r in rows:
        if r["employee_id"] not in status:
            status[r["employee_id"]] = loan_status(r["employee_id"])
    rows = [dict(r, balance=status[r["employee_id"]].get(r["id"], (0, r["amount"]))[1]) for r in rows]
    return render_template("hr/loans.html", rows=rows, branches=branch_map())


@bp.route("/loans/new")
@bp.route("/loans/<int:lid>")
@login_required
def loan_form(lid=None):
    ln = get_loan(lid) if lid else {"employee_id": request.args.get("employee", type=int), "state": "draft",
                                    "date": date.today().isoformat(), "start_period": date.today().isoformat()[:7]}
    journals = journals_for([ln["branch_id"]] if lid else context_branch_ids(), ("bank", "cash"))
    repaid = loan_status(ln["employee_id"]).get(lid, (0, 0))[0] if lid else 0
    return render_template("hr/loan_form.html", ln=ln, employees=employees_in_scope(active_only=True),
                           journals=journals, branches=branch_map(), repaid=repaid)


@bp.route("/loans/save", methods=["POST"])
@write_required
def loan_save():
    f = request.form
    ln = get_loan(int(f["lid"])) if f.get("lid") else None
    if ln and ln["state"] != "draft":
        flash(t("Only draft documents can be edited."), "error")
        return redirect(url_for("hr.loan_form", lid=ln["id"]))
    try:
        emp = get_employee(int(f["employee_id"]))
        vals = {"employee_id": emp["id"], "branch_id": emp["branch_id"], "date": f["date"], "amount": to_int(f["amount"]),
                "installment": to_int(f["installment"]), "start_period": f["start_period"][:7], "notes": f.get("notes")}
        if vals["amount"] <= 0 or vals["installment"] <= 0:
            raise AccountingError(t("The amount must be greater than zero."))
        vals["installment"] = min(vals["installment"], vals["amount"])
        if ln:
            x(f"UPDATE employee_loans SET {', '.join(k + ' = ?' for k in vals)} WHERE id = ?", list(vals.values()) + [ln["id"]])
            lid = ln["id"]
        else:
            vals.update(number=next_number("employee_loans", "number", "LOAN", emp["branch_id"], vals["date"]),
                        state="draft", created_by=user_id(), created_at=now())
            lid = x(f"INSERT INTO employee_loans ({', '.join(vals)}) VALUES ({', '.join('?' * len(vals))})",
                    list(vals.values()))
        if f.get("action") == "disburse":
            disburse_loan(lid, f["journal_id"], user_id())
        audit(user_id(), "save", "loan", lid)
        commit()
        flash(t("Saved."), "success")
        return redirect(url_for("hr.loan_form", lid=lid))
    except (AccountingError, ValueError, KeyError) as ex:
        get_db().rollback()
        flash(str(ex), "error")
        return redirect(request.referrer or url_for("hr.loans"))


@bp.route("/loans/<int:lid>/<action>", methods=["POST"])
@write_required
def loan_action(lid, action):
    ln = get_loan(lid)
    try:
        if action == "disburse":
            disburse_loan(lid, request.form["journal_id"], user_id())
        elif action == "delete" and ln["state"] == "draft":
            x("DELETE FROM employee_loans WHERE id = ?", (lid,))
            commit()
            return redirect(url_for("hr.loans"))
        else:
            raise AccountingError(t("This action is not allowed in the current state."))
        commit()
        flash(t("Done."), "success")
    except (AccountingError, KeyError, ValueError) as ex:
        get_db().rollback()
        flash(str(ex), "error")
    return redirect(url_for("hr.loan_form", lid=lid))


# ---------------------------------------------------------------- payroll runs

def get_run(rid):
    run = q("SELECT * FROM payroll_runs WHERE id = ?", (rid,), one=True)
    if not run:
        abort(404)
    check_branch(run["branch_id"])
    return run


def run_lines(rid):
    return q("SELECT l.*, e.code, e.name_en, e.name_ar, e.iban, e.bank_name, e.id_number, e.job_title, e.department "
             "FROM payroll_lines l JOIN employees e ON e.id = l.employee_id WHERE l.run_id = ? ORDER BY e.code", (rid,))


@bp.route("/payroll")
@login_required
def payroll():
    ids = context_branch_ids()
    rows = []
    if ids:
        rows = q(f"SELECT r.*, (SELECT COUNT(*) FROM payroll_lines WHERE run_id = r.id) AS n, "
                 f"(SELECT COALESCE(SUM(gross), 0) FROM payroll_lines WHERE run_id = r.id) AS gross, "
                 f"(SELECT COALESCE(SUM(net), 0) FROM payroll_lines WHERE run_id = r.id) AS net "
                 f"FROM payroll_runs r WHERE r.branch_id IN ({placeholders(ids)}) ORDER BY r.period DESC, r.branch_id",
                 list(ids))
    last = date.today().replace(day=1)
    return render_template("hr/payroll.html", rows=rows, branches=branch_map(), default_branch=default_branch(),
                           period=last.isoformat()[:7])


@bp.route("/payroll/new", methods=["POST"])
@write_required
def payroll_new():
    try:
        bid = check_branch(request.form["branch_id"])
        period = request.form["period"][:7]
        date.fromisoformat(period + "-01")
        rid = create_run(bid, period, user_id())
        audit(user_id(), "create", "payroll", rid)
        commit()
        return redirect(url_for("hr.run", rid=rid))
    except (AccountingError, ValueError, KeyError) as ex:
        get_db().rollback()
        flash(str(ex), "error")
        return redirect(url_for("hr.payroll"))


@bp.route("/payroll/<int:rid>")
@login_required
def run(rid):
    r = get_run(rid)
    branch = q("SELECT * FROM branches WHERE id = ?", (r["branch_id"],), one=True)
    if request.args.get("export"):
        from .. import currency_decimals
        from ..exports import Report, respond
        rep = Report("Payroll Register", [f"{t('Month')} {r['period']}", f"{branch['code']} {nm(branch)}",
                                          f"{t('Amounts in')} {branch['currency']}"],
                     currency_decimals(branch["currency"]), landscape=True, signatures=True)
        rep.subtitle = r["number"]
        rep.column(t("Employee"), width=2.2).column(t("Days"), "qty", 0.5).column(t("Basic"), "num").column(t("Housing"), "num") \
            .column(t("Transport + other"), "num").column(t("Overtime"), "num").column(t("Additions"), "num") \
            .column(t("Deductions"), "num").column(t("Gross"), "num").column(t("GOSI"), "num").column(t("Loan"), "num") \
            .column(t("Net pay"), "num")
        lines = run_lines(rid)
        for ln in lines:
            rep.row([f"{ln['code']}  {nm(ln)}", ln["days"], ln["basic"], ln["housing"], ln["transport"] + ln["other"],
                     ln["overtime"] or None, ln["additions"] or None, (ln["deductions"] + ln["unpaid_deduction"]) or None,
                     ln["gross"], ln["gosi_employee"] or None, ln["loan_deduction"] or None, ln["net"]])
        tot = run_totals(rid)
        rep.row([t("Total"), None, tot["basic"], tot["housing"], tot["transport"] + tot["other"], tot["overtime"],
                 tot["additions"], tot["deductions"] + tot["unpaid_deduction"], tot["gross"], tot["gosi_employee"],
                 tot["loan_deduction"], tot["net"]], "grand")
        rep.notes.append(f"{t('GOSI')} — {t('Company')}: {tot['gosi_employer'] / 1000:,.2f}  ·  {t('End of service')}: "
                         f"{tot['eosb_provision'] / 1000:,.2f}  ·  {t('Vacation')}: {tot['leave_provision'] / 1000:,.2f}")
        out = respond(rep, r["number"].replace("/", "-"))
        if out:
            return out
    return render_template("hr/run.html", r=r, lines=run_lines(rid), totals=run_totals(rid), branch=branch,
                           cur=branch["currency"], journals=journals_for([r["branch_id"]], ("bank", "cash")),
                           today=date.today().isoformat())


@bp.route("/payroll/<int:rid>/<action>", methods=["POST"])
@write_required
def run_action(rid, action):
    r = get_run(rid)
    f = request.form
    try:
        if action in ("save", "post") and r["state"] == "draft":
            for ln in q("SELECT id FROM payroll_lines WHERE run_id = ?", (rid,)):
                k = ln["id"]
                if f"ot_{k}" in f:
                    x("UPDATE payroll_lines SET ot_hours = ?, additions = ?, deductions = ?, unpaid_days = ?, note = ? "
                      "WHERE id = ?", (float(f.get(f"ot_{k}") or 0), to_int(f.get(f"add_{k}")), to_int(f.get(f"ded_{k}")),
                                       float(f.get(f"unp_{k}") or 0), f.get(f"note_{k}") or "", k))
            calculate_run(rid)
            if action == "post":
                post_run(rid, user_id())
        elif action == "recalculate" and r["state"] == "draft":
            calculate_run(rid)
        elif action == "pay":
            pay("payroll_runs", rid, f["journal_id"], f.get("date") or date.today().isoformat(), user_id())
        elif action == "delete" and r["state"] == "draft":
            x("DELETE FROM payroll_runs WHERE id = ?", (rid,))
            audit(user_id(), "delete", "payroll", rid, r["number"])
            commit()
            flash(t("Deleted."), "success")
            return redirect(url_for("hr.payroll"))
        else:
            raise AccountingError(t("This action is not allowed in the current state."))
        audit(user_id(), action, "payroll", rid, r["number"])
        commit()
        flash(t("Done."), "success")
    except (AccountingError, ValueError, KeyError) as ex:
        get_db().rollback()
        flash(str(ex), "error")
    return redirect(url_for("hr.run", rid=rid))


@bp.route("/payroll/<int:rid>/wps.csv")
@login_required
def wps(rid):
    """Salary transfer file for the bank (WPS / Mudad layout: one row per employee)."""
    r = get_run(rid)
    dec = branch_decimals(r["branch_id"])
    out = io.StringIO()
    w = csv.writer(out)
    w.writerow(["Employee No", "Name", "ID / Iqama", "Bank", "IBAN", "Basic", "Housing", "Other earnings",
                "Deductions", "Net pay", "Period"])
    m = lambda v: f"{v / 1000:.{dec}f}"
    for ln in run_lines(rid):
        other = ln["transport"] + ln["other"] + ln["overtime"] + ln["additions"]
        ded = ln["deductions"] + ln["unpaid_deduction"] + ln["gosi_employee"] + ln["loan_deduction"]
        w.writerow([ln["code"], ln["name_en"], ln["id_number"] or "", ln["bank_name"] or "", ln["iban"] or "",
                    m(ln["basic"]), m(ln["housing"]), m(other), m(ded), m(ln["net"]), r["period"]])
    return Response("﻿" + out.getvalue(), mimetype="text/csv",
                    headers={"Content-Disposition": f"attachment; filename=WPS-{r['number'].replace('/', '-')}.csv"})


@bp.route("/payslip/<int:line_id>")
@login_required
def payslip(line_id):
    ln = q("SELECT l.*, e.code, e.name_en, e.name_ar, e.iban, e.bank_name, e.id_number, e.job_title, e.department, "
           "e.branch_id FROM payroll_lines l JOIN employees e ON e.id = l.employee_id WHERE l.id = ?", (line_id,), one=True)
    if not ln:
        abort(404)
    r = get_run(ln["run_id"])
    branch = q("SELECT * FROM branches WHERE id = ?", (r["branch_id"],), one=True)
    if request.args.get("export"):
        from .. import currency_decimals
        from ..exports import Report, respond
        rep = Report("Payslip", decimals=currency_decimals(branch["currency"]), signatures=True)
        rep.subtitle = f"{t('Month')} {r['period']}"
        rep.details = [("Employee", f"{ln['code']}  {nm(ln)}"), ("Job title", ln["job_title"] or "—"),
                       ("Department", ln["department"] or "—"), ("ID / Iqama", ln["id_number"] or "—"),
                       ("Bank", f"{ln['bank_name'] or ''} {ln['iban'] or ''}".strip() or "—"),
                       ("Days paid", f"{ln['days']:g} / 30")]
        rep.column(t("Earnings"), width=2).column(t("Amount"), "num").column(t("Deductions"), width=2).column(t("Amount"), "num")
        earn = [(t("Basic salary"), ln["basic"]), (t("Housing allowance"), ln["housing"]), (t("Transport allowance"), ln["transport"]),
                (t("Other allowances"), ln["other"]), (f"{t('Overtime')} ({ln['ot_hours']:g} h)", ln["overtime"]),
                (t("Additions"), ln["additions"])]
        ded = [(f"{t('Unpaid leave')} ({ln['unpaid_days']:g})", ln["unpaid_deduction"]), (t("Other deductions"), ln["deductions"]),
               (t("GOSI (employee share)"), ln["gosi_employee"]), (t("Loan instalment"), ln["loan_deduction"])]
        earn = [e for e in earn if e[1]]
        ded = [d for d in ded if d[1]] or [(t("GOSI (employee share)"), 0)]
        for i in range(max(len(earn), len(ded))):
            e = earn[i] if i < len(earn) else ("", None)
            d = ded[i] if i < len(ded) else ("", None)
            rep.row([e[0], e[1], d[0], d[1]])
        rep.row([t("Total earnings"), sum(e[1] for e in earn), t("Total deductions"), sum(d[1] for d in ded)], "subtotal")
        rep.totals = [(f"{t('Net pay')} ({branch['currency']})", ln["net"], True)]
        out = respond(rep, f"Payslip-{ln['code']}-{r['period']}")
        if out:
            return out
    return render_template("hr/payslip.html", ln=ln, r=r, branch=branch, cur=branch["currency"])


# ---------------------------------------------------------------- final settlements

def get_settlement(sid):
    s = q("SELECT s.*, e.code, e.name_en, e.name_ar, e.join_date, e.law FROM eos_settlements s "
          "JOIN employees e ON e.id = s.employee_id WHERE s.id = ?", (sid,), one=True)
    if not s:
        abort(404)
    check_branch(s["branch_id"])
    return s


@bp.route("/settlements")
@login_required
def settlements():
    ids = context_branch_ids()
    rows = []
    if ids:
        rows = q(f"SELECT s.*, e.code, e.name_en, e.name_ar FROM eos_settlements s JOIN employees e ON e.id = s.employee_id "
                 f"WHERE s.branch_id IN ({placeholders(ids)}) ORDER BY s.leave_date DESC", list(ids))
    return render_template("hr/settlements.html", rows=rows, reasons=REASONS, branches=branch_map())


@bp.route("/settlements/new", methods=["GET", "POST"])
@write_required
def settlement_new():
    if request.method == "POST":
        f = request.form
        try:
            e = get_employee(int(f["employee_id"]))
            if e["state"] != "active":
                raise AccountingError(t("This employee has already left."))
            if q("SELECT 1 FROM eos_settlements WHERE employee_id = ? AND state = 'draft'", (e["id"],), one=True):
                raise AccountingError(t("A draft settlement already exists for this employee."))
            reason = f["reason"] if f.get("reason") in REASONS else "termination"
            if f["leave_date"] < e["join_date"]:
                raise AccountingError(t("The last working day is before the joining date."))
            vals = compute_settlement(e, f["leave_date"], reason, to_int(f.get("additions")), to_int(f.get("deductions")))
            vals.update(employee_id=e["id"], branch_id=e["branch_id"], leave_date=f["leave_date"], reason=reason,
                        notes=f.get("notes"), state="draft", created_by=user_id(), created_at=now(),
                        number=next_number("eos_settlements", "number", "EOS", e["branch_id"], f["leave_date"]))
            sid = x(f"INSERT INTO eos_settlements ({', '.join(vals)}) VALUES ({', '.join('?' * len(vals))})",
                    list(vals.values()))
            audit(user_id(), "create", "settlement", sid)
            commit()
            return redirect(url_for("hr.settlement", sid=sid))
        except (AccountingError, ValueError, KeyError) as ex:
            get_db().rollback()
            flash(str(ex), "error")
    return render_template("hr/settlement_form.html", employees=employees_in_scope(active_only=True), reasons=REASONS,
                           employee_id=request.args.get("employee", type=int), today=date.today().isoformat())


@bp.route("/settlements/<int:sid>")
@login_required
def settlement(sid):
    s = get_settlement(sid)
    e = get_employee(s["employee_id"])
    if request.args.get("export"):
        from .. import currency_decimals
        from ..exports import Report, respond
        cur = currency_of(s["branch_id"])
        rep = Report("Final Settlement", decimals=currency_decimals(cur), signatures=True)
        rep.subtitle = s["number"]
        rep.details = [("Employee", f"{s['code']}  {nm(s)}"), ("Reason", t(REASONS[s["reason"]])),
                       ("Joining date", s["join_date"]), ("Last working day", s["leave_date"]),
                       ("Service", f"{s['years']:.2f} {t('years')}"), ("Labour law", t(LAWS[s["law"]])),
                       ("Wage used", f"{s['wage'] / 1000:,.2f} {cur}")]
        rep.column(t("Description"), width=3).column(t("Amount"), "num")
        rep.row([t("End-of-service benefit (full)"), s["eosb_full"]])
        rep.row([t("End-of-service benefit due"), s["eosb_due"]], "group")
        rep.row([f"{t('Unused leave')} ({s['leave_days']:.1f} {t('days')})", s["leave_pay"]])
        if s["additions"]:
            rep.row([t("Other additions"), s["additions"]])
        if s["deductions"]:
            rep.row([t("Other deductions"), -s["deductions"]])
        if s["loan_balance"]:
            rep.row([t("Loan balance recovered"), -s["loan_balance"]])
        rep.totals = [(f"{t('Net payable to the employee')} ({cur})", s["net"], True)]
        rep.notes.append(t("I confirm receipt of the above amount in full and final settlement of all my dues."))
        out = respond(rep, s["number"].replace("/", "-"))
        if out:
            return out
    return render_template("hr/settlement.html", s=s, e=e, cur=currency_of(s["branch_id"]), reasons=REASONS, laws=LAWS,
                           journals=journals_for([s["branch_id"]], ("bank", "cash")), today=date.today().isoformat())


@bp.route("/settlements/<int:sid>/<action>", methods=["POST"])
@write_required
def settlement_action(sid, action):
    s = get_settlement(sid)
    try:
        if action == "post":
            post_settlement(sid, user_id())
        elif action == "pay":
            pay("eos_settlements", sid, request.form["journal_id"], request.form.get("date") or date.today().isoformat(),
                user_id())
        elif action == "delete" and s["state"] == "draft":
            x("DELETE FROM eos_settlements WHERE id = ?", (sid,))
            commit()
            return redirect(url_for("hr.settlements"))
        else:
            raise AccountingError(t("This action is not allowed in the current state."))
        commit()
        flash(t("Done."), "success")
    except (AccountingError, KeyError, ValueError) as ex:
        get_db().rollback()
        flash(str(ex), "error")
    return redirect(url_for("hr.settlement", sid=sid))


# ---------------------------------------------------------------- reports

@bp.route("/reports/provisions")
@login_required
def provisions():
    """End-of-service and vacation obligations per employee today, against what the ledger holds."""
    today = date.today().isoformat()
    rows = []
    for e in employees_in_scope(active_only=True):
        years, wage, full, _ = eosb(e, today, "termination")
        bal = leave_balance(e, today)
        rows.append({"e": e, "years": years, "wage": wage, "eosb": int(full), "eosb_booked": e["eosb_booked"],
                     "leave": bal, "leave_value": int(max(bal, 0) * leave_daily(e)), "leave_booked": e["leave_booked"],
                     "cur": currency_of(e["branch_id"])})
    from ..exports import Report, respond
    curs = sorted({r["cur"] for r in rows}) or ["SAR"]
    rep = Report("End of Service & Leave", [f"{t('As of')} {today}", ", ".join(curs)], landscape=True)
    rep.column(t("Employee"), width=2.4).column(t("Currency"), width=0.6).column(t("Years"), "qty", 0.6) \
        .column(t("Wage used"), "num").column(t("End of service today"), "num").column(t("Booked"), "num") \
        .column(t("Leave days"), "qty", 0.7).column(t("Leave value"), "num").column(t("Booked"), "num")
    for cur in curs:
        part = [r for r in rows if r["cur"] == cur]
        if len(curs) > 1:
            rep.section(cur)
        for r in part:
            rep.row([f"{r['e']['code']}  {nm(r['e'])}", cur, round(r["years"], 2), r["wage"], r["eosb"], r["eosb_booked"],
                     round(r["leave"], 1), r["leave_value"], r["leave_booked"]])
        rep.row([t("Total"), cur, None, None, sum(r["eosb"] for r in part), sum(r["eosb_booked"] for r in part), None,
                 sum(r["leave_value"] for r in part), sum(r["leave_booked"] for r in part)], "grand")
    out = respond(rep, "End_of_Service_and_Leave")
    if out:
        return out
    return render_template("hr/provisions.html", rows=rows, today=today)


@bp.route("/reports/documents")
@login_required
def documents():
    days = request.args.get("days", 60, type=int)
    rows = expiring_documents(context_branch_ids(), days)
    from ..exports import Report, respond
    rep = Report("Expiring Documents", [f"{t('Expiring within')} {days} {t('days')}", date.today().isoformat()])
    rep.column(t("Employee"), width=2.4).column(t("Document"), width=1.2).column(t("Number"), width=1.4).column(t("Expiry"), width=1)
    for r in rows:
        e = r["employee"]
        rep.row([f"{e['code']}  {nm(e)}", t(r["doc"]), e["id_number"] if r["doc"] != "Passport" else e["passport_no"],
                 r["expiry"] + (f"  ({t('Expired')})" if r["expired"] else "")])
    return respond(rep, "Expiring_Documents") or render_template("hr/documents.html", rows=rows, days=days,
                                                                  branches=branch_map())


# ---------------------------------------------------------------- settings

@bp.route("/settings", methods=["GET", "POST"])
@admin_required
def hr_settings():
    if request.method == "POST":
        f = request.form
        try:
            vals = {k: float(f.get(k) or 0) for k in ("gosi_national_employee", "gosi_national_employer",
                                                      "gosi_expat_employer")}
            vals["gosi_cap"] = to_int(f.get("gosi_cap"))
            vals["eosb_base"] = f.get("eosb_base") if f.get("eosb_base") in EOSB_BASES else "total"
            for col, *_ in HR_ACCOUNTS:
                vals[col] = int(f[col]) if f.get(col) else None
            x(f"UPDATE hr_settings SET {', '.join(k + ' = ?' for k in vals)} WHERE id = 1", list(vals.values()))
            audit(user_id(), "save", "hr_settings", 1)
            commit()
            flash(t("Saved."), "success")
        except ValueError as ex:
            get_db().rollback()
            flash(str(ex), "error")
        return redirect(url_for("hr.hr_settings"))
    return render_template("hr/settings.html", st=settings(), bases=EOSB_BASES, accounts=accounts_list(),
                           fields=HR_ACCOUNTS)
