"""HR & Payroll: monthly salaries, social insurance, leave, end-of-service benefits and employee loans.

Labour law per employee (Saudi Arabia or UAE):

                      Saudi Arabia (KSA)                          UAE
Annual leave          21 days a year, 30 after 5 years             30 days a year
End of service        ½ month per year for the first 5 years,     21 days' basic per year for the first 5 years,
                      1 month per year after; on resignation       30 days after; nothing under 1 year; at most
                      nothing under 2 years, ⅓ for 2-5, ⅔ for      2 years' wage
                      5-10, all after 10
Social insurance      GOSI on basic + housing (capped)             none here (nationals use their own scheme)
Overtime (per hour)   hourly wage + 50% of hourly basic            hourly basic + 25%

Every month a payroll run per branch posts one entry (at the month end):
    Dr Salaries & Wages            gross pay                      (by cost centre)
    Dr GOSI Contributions          employer share
        Cr GOSI Payable            employee + employer share
        Cr Employee Loans          loan instalments deducted
        Cr Salaries Payable        net pay
    Dr End of Service Benefits     Cr End of Service Provision    the month's increase in the obligation
    Dr Vacation Pay                Cr Vacation Pay Provision      the month's change in unused leave
The provisions are trued up to the full obligation at the month end, so a pay rise or leave taken corrects itself.
Paying the run: Dr Salaries Payable / Cr Bank. A final settlement uses up the employee's provisions and books only
the difference as an expense (or a release, when a resignation pays less than was provided).
"""
import calendar
from datetime import date, timedelta

from .db import placeholders, q, x
from .i18n import nm, t
from .ledger import AccountingError, audit, create_move, now
from .money import round_cur
from .schedules import branch_decimals
from .stock import next_number

LAWS = {"ksa": "Saudi Arabia", "uae": "UAE"}
LEAVE_KINDS = {"annual": "Annual leave", "sick": "Sick leave", "unpaid": "Unpaid leave", "other": "Other leave"}
REASONS = {"resignation": "Resignation", "termination": "Termination by the employer",
           "contract_end": "End of contract", "other_full": "Other (full benefit)"}
EOSB_BASES = {"total": "Total monthly wage (basic + all allowances)", "basic_housing": "Basic + housing",
              "basic": "Basic only"}


def settings():
    return q("SELECT * FROM hr_settings WHERE id = 1", one=True)


def month_bounds(period):
    y, m = int(period[:4]), int(period[5:7])
    return date(y, m, 1), date(y, m, calendar.monthrange(y, m)[1])


def monthly_wage(e):
    return e["basic"] + e["housing"] + e["transport"] + e["other_allowance"]


def service_days(e, end):
    start = date.fromisoformat(e["join_date"])
    end = date.fromisoformat(end) if isinstance(end, str) else end
    return max((end - start).days + 1, 0)


def service_years(e, end):
    return service_days(e, end) / 365.0


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

def annual_days(e, on):
    if e["annual_leave_days"]:
        return e["annual_leave_days"]
    if e["law"] == "uae":
        return 30
    return 30 if service_years(e, on) >= 5 else 21


def leave_accrued(e, on):
    """Leave days earned from the joining date to `on`."""
    days = service_days(e, on)
    if e["annual_leave_days"]:
        return e["annual_leave_days"] * days / 365.0
    if e["law"] == "uae":
        return 30 * days / 365.0
    first = min(days, 5 * 365)
    return (21 * first + 30 * (days - first)) / 365.0


def leave_taken(employee_id, on, kind="annual", exclude=None):
    r = q("SELECT COALESCE(SUM(days), 0) FROM leave_requests WHERE employee_id = ? AND kind = ? AND state = 'approved' "
          "AND date_from <= ? AND id != ?", (employee_id, kind, on, exclude or -1), one=True)
    return r[0]


def leave_balance(e, on):
    on = on if isinstance(on, str) else on.isoformat()
    return round(e["leave_opening"] + leave_accrued(e, on) - leave_taken(e["id"], on), 2)


def leave_daily(e):
    """Pay for one day of leave: the full wage in Saudi Arabia, basic + housing in the UAE (30-day month)."""
    base = monthly_wage(e) if e["law"] != "uae" else e["basic"] + e["housing"]
    return base / 30.0


def leave_days(d1, d2):
    return (date.fromisoformat(d2) - date.fromisoformat(d1)).days + 1


# ---------------------------------------------------------------- end of service

def eosb_wage(e, st=None):
    if e["law"] == "uae":
        return e["basic"]
    base = (st or settings())["eosb_base"]
    if base == "basic":
        return e["basic"]
    if base == "basic_housing":
        return e["basic"] + e["housing"]
    return monthly_wage(e)


def eosb(e, end, reason="termination", st=None):
    """(years of service, wage used, full benefit, benefit due for this reason), amounts in mils (unrounded)."""
    years = service_years(e, end)
    wage = eosb_wage(e, st)
    if e["law"] == "uae":
        if years < 1:
            return years, wage, 0, 0
        daily = wage / 30.0
        full = min(21 * daily * min(years, 5) + 30 * daily * max(years - 5, 0), 24 * wage)
        return years, wage, full, full
    full = wage / 2.0 * min(years, 5) + wage * max(years - 5, 0)
    factor = 1.0
    if reason == "resignation":
        factor = 0 if years < 2 else 1 / 3.0 if years < 5 else 2 / 3.0 if years < 10 else 1.0
    return years, wage, full, full * factor


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

def loan_status(employee_id, upto_period=None):
    """{loan id: (repaid, balance)}: deductions applied to the oldest loan first."""
    loans = q("SELECT * FROM employee_loans WHERE employee_id = ? AND state IN ('running', 'done') ORDER BY date, id",
              (employee_id,))
    paid = q("SELECT COALESCE(SUM(l.loan_deduction), 0) FROM payroll_lines l JOIN payroll_runs r ON r.id = l.run_id "
             "WHERE l.employee_id = ? AND r.state IN ('posted', 'paid')", (employee_id,), one=True)[0]
    paid += q("SELECT COALESCE(SUM(loan_balance), 0) FROM eos_settlements WHERE employee_id = ? "
              "AND state IN ('posted', 'paid')", (employee_id,), one=True)[0]
    out = {}
    for ln in loans:
        used = min(paid, ln["amount"])
        paid -= used
        out[ln["id"]] = (used, ln["amount"] - used)
    return out


def loan_balance(employee_id):
    return sum(b for _, b in loan_status(employee_id).values())


def loan_due(employee_id, period):
    """Instalments due this month from loans still open."""
    status = loan_status(employee_id)
    due = 0
    for ln in q("SELECT * FROM employee_loans WHERE employee_id = ? AND state = 'running' AND start_period <= ?",
                (employee_id, period)):
        due += min(ln["installment"], status.get(ln["id"], (0, 0))[1])
    return due


# ---------------------------------------------------------------- payroll lines

def compute_line(e, period, inputs, st, dec):
    """One employee's pay for a month. inputs: ot_hours, additions, deductions, unpaid_days (kept on recalculation)."""
    start, end = month_bounds(period)
    first = max(start, date.fromisoformat(e["join_date"]))
    last = min(end, date.fromisoformat(e["leave_date"])) if e["leave_date"] else end
    worked = max((last - first).days + 1, 0)
    days = 30.0 if worked >= (end - start).days + 1 else min(worked, 30)
    r = lambda v: round_cur(int(round(v)), dec)
    ln = {"employee_id": e["id"], "days": days}
    for col, src in (("basic", "basic"), ("housing", "housing"), ("transport", "transport"), ("other", "other_allowance")):
        ln[col] = r(e[src] * days / 30.0)
    wage = monthly_wage(e)
    ot_hours = float(inputs.get("ot_hours") or 0)
    hourly_basic = e["basic"] / 30.0 / 8
    if e["law"] == "uae":
        ln["overtime"] = r(ot_hours * hourly_basic * 1.25)
    else:
        ln["overtime"] = r(ot_hours * (wage / 30.0 / 8 + 0.5 * hourly_basic))
    ln["ot_hours"] = ot_hours
    ln["additions"] = int(inputs.get("additions") or 0)
    ln["deductions"] = int(inputs.get("deductions") or 0)
    ln["unpaid_days"] = float(inputs.get("unpaid_days") or 0)
    ln["unpaid_deduction"] = r(wage / 30.0 * ln["unpaid_days"])
    ln["note"] = inputs.get("note") or ""
    ln["gross"] = (ln["basic"] + ln["housing"] + ln["transport"] + ln["other"] + ln["overtime"] + ln["additions"]
                   - ln["deductions"] - ln["unpaid_deduction"])
    gosi_emp = gosi_er = 0
    if e["law"] == "ksa":
        base = min((e["basic"] + e["housing"]) * days / 30.0, st["gosi_cap"])
        if e["is_national"]:
            gosi_emp, gosi_er = base * st["gosi_national_employee"] / 100, base * st["gosi_national_employer"] / 100
        else:
            gosi_er = base * st["gosi_expat_employer"] / 100
    ln["gosi_employee"], ln["gosi_employer"] = r(gosi_emp), r(gosi_er)
    ln["loan_deduction"] = min(loan_due(e["id"], period), max(ln["gross"] - ln["gosi_employee"], 0))
    ln["net"] = ln["gross"] - ln["gosi_employee"] - ln["loan_deduction"]
    # provisions trued up to the obligation at the month end; leavers are settled separately
    if e["leave_date"] and e["leave_date"] <= end.isoformat():
        ln.update(eosb_total=e["eosb_booked"], eosb_provision=0, leave_balance=0, leave_total=e["leave_booked"],
                  leave_provision=0)
    else:
        ln["eosb_total"] = r(eosb(e, end, "termination", st)[2])
        ln["eosb_provision"] = ln["eosb_total"] - e["eosb_booked"]
        ln["leave_balance"] = leave_balance(e, end)
        ln["leave_total"] = r(max(ln["leave_balance"], 0) * leave_daily(e))
        ln["leave_provision"] = ln["leave_total"] - e["leave_booked"]
    return ln


LINE_COLS = ("employee_id", "days", "basic", "housing", "transport", "other", "ot_hours", "overtime", "additions",
             "deductions", "unpaid_days", "unpaid_deduction", "loan_deduction", "gosi_employee", "gosi_employer",
             "gross", "net", "eosb_total", "eosb_provision", "leave_balance", "leave_total", "leave_provision", "note")


def run_employees(branch_id, period):
    start, end = month_bounds(period)
    return q("SELECT * FROM employees WHERE branch_id = ? AND join_date <= ? AND (leave_date IS NULL OR leave_date >= ?) "
             "ORDER BY code", (branch_id, end.isoformat(), start.isoformat()))


def unpaid_leave_days(employee_id, period):
    start, end = month_bounds(period)
    total = 0
    for lv in q("SELECT * FROM leave_requests WHERE employee_id = ? AND kind = 'unpaid' AND state = 'approved' "
                "AND date_from <= ? AND date_to >= ?", (employee_id, end.isoformat(), start.isoformat())):
        a = max(date.fromisoformat(lv["date_from"]), start)
        b = min(date.fromisoformat(lv["date_to"]), end)
        total += (b - a).days + 1
    return total


def calculate_run(run_id):
    """(Re)build a draft run's lines from the employee records, keeping what was typed in (overtime, additions...)."""
    run = q("SELECT * FROM payroll_runs WHERE id = ?", (run_id,), one=True)
    if run["state"] != "draft":
        raise AccountingError(t("This action is not allowed in the current state."))
    kept = {r["employee_id"]: dict(r) for r in q("SELECT * FROM payroll_lines WHERE run_id = ?", (run_id,))}
    st, dec = settings(), branch_decimals(run["branch_id"])
    x("DELETE FROM payroll_lines WHERE run_id = ?", (run_id,))
    n = 0
    for e in run_employees(run["branch_id"], run["period"]):
        inputs = kept.get(e["id"]) or {"unpaid_days": unpaid_leave_days(e["id"], run["period"])}
        ln = compute_line(e, run["period"], inputs, st, dec)
        x(f"INSERT INTO payroll_lines (run_id, {', '.join(LINE_COLS)}) VALUES (?, {', '.join('?' * len(LINE_COLS))})",
          [run_id] + [ln[c] for c in LINE_COLS])
        n += 1
    return n


def create_run(branch_id, period, user_id=None):
    start, end = month_bounds(period)
    if q("SELECT 1 FROM payroll_runs WHERE branch_id = ? AND period = ?", (branch_id, period), one=True):
        raise AccountingError(t("A payroll for this branch and month already exists."))
    rid = x("INSERT INTO payroll_runs (number, branch_id, period, date, state, created_by, created_at) "
            "VALUES (?,?,?,?, 'draft', ?, ?)",
            (next_number("payroll_runs", "number", "PAY", branch_id, end.isoformat()), branch_id, period,
             end.isoformat(), user_id, now()))
    if not calculate_run(rid):
        raise AccountingError(t("No employees on the payroll of this branch for the month."))
    return rid


def run_totals(run_id):
    cols = ("basic", "housing", "transport", "other", "overtime", "additions", "deductions", "unpaid_deduction",
            "loan_deduction", "gosi_employee", "gosi_employer", "gross", "net", "eosb_provision", "leave_provision")
    r = q(f"SELECT COUNT(*) AS n, {', '.join(f'COALESCE(SUM({c}), 0) AS {c}' for c in cols)} FROM payroll_lines "
          f"WHERE run_id = ?", (run_id,), one=True)
    return dict(r)


def hr_journal(branch_id):
    j = q("SELECT id FROM journals WHERE branch_id = ? AND type = 'general' AND active = 1 "
          "ORDER BY code = 'MISC' DESC, id LIMIT 1", (branch_id,), one=True)
    if not j:
        raise AccountingError(t("The branch needs a miscellaneous (general) journal for payroll entries."))
    return j["id"]


class Entry:
    """Collects signed amounts (positive = debit) per account and cost centre and turns them into entry lines."""
    def __init__(self, label):
        self.label, self.sums = label, {}

    def add(self, account_id, amount, cc=None):
        if amount:
            self.sums[(account_id, cc)] = self.sums.get((account_id, cc), 0) + amount

    def lines(self):
        return [{"account_id": a, "cost_centre_id": cc, "label": self.label,
                 "debit": v if v > 0 else 0, "credit": -v if v < 0 else 0}
                for (a, cc), v in self.sums.items() if v]


def _accounts(st):
    need = ("salary_expense_id", "gosi_expense_id", "eosb_expense_id", "leave_expense_id", "salary_payable_id",
            "gosi_payable_id", "eosb_provision_id", "leave_provision_id", "loan_account_id")
    if any(not st[k] for k in need):
        raise AccountingError(t("Set the payroll accounts in HR Settings first."))
    return st


def post_run(run_id, user_id=None):
    run = q("SELECT * FROM payroll_runs WHERE id = ?", (run_id,), one=True)
    if run["state"] != "draft":
        raise AccountingError(t("This action is not allowed in the current state."))
    calculate_run(run_id)
    st = _accounts(settings())
    lines = q("SELECT l.*, e.cost_centre_id AS cc FROM payroll_lines l JOIN employees e ON e.id = l.employee_id "
              "WHERE l.run_id = ?", (run_id,))
    if not lines:
        raise AccountingError(t("No employees on the payroll of this branch for the month."))
    en = Entry(f"{t('Payroll')} {run['period']}")
    for ln in lines:
        cc = ln["cc"]
        en.add(st["salary_expense_id"], ln["gross"], cc)
        en.add(st["gosi_expense_id"], ln["gosi_employer"], cc)
        en.add(st["gosi_payable_id"], -(ln["gosi_employee"] + ln["gosi_employer"]))
        en.add(st["loan_account_id"], -ln["loan_deduction"])
        en.add(st["salary_payable_id"], -ln["net"])
        en.add(st["eosb_expense_id"], ln["eosb_provision"], cc)
        en.add(st["eosb_provision_id"], -ln["eosb_provision"])
        en.add(st["leave_expense_id"], ln["leave_provision"], cc)
        en.add(st["leave_provision_id"], -ln["leave_provision"])
    move_id = create_move(run["branch_id"], hr_journal(run["branch_id"]), run["date"], en.lines(), user_id,
                          ref=run["number"], narration=f"{t('Payroll')} {run['period']}", post=True)
    for ln in lines:
        x("UPDATE employees SET eosb_booked = eosb_booked + ?, leave_booked = leave_booked + ? WHERE id = ?",
          (ln["eosb_provision"], ln["leave_provision"], ln["employee_id"]))
        close_repaid_loans(ln["employee_id"])
    x("UPDATE payroll_runs SET state = 'posted', move_id = ?, posted_by = ? WHERE id = ?", (move_id, user_id, run_id))
    audit(user_id, "post", "payroll", run_id, run["number"])
    return move_id


def close_repaid_loans(employee_id):
    for lid, (_, bal) in loan_status(employee_id).items():
        if bal <= 0:
            x("UPDATE employee_loans SET state = 'done' WHERE id = ? AND state = 'running'", (lid,))


def pay_journal_account(branch_id, journal_id):
    j = q("SELECT * FROM journals WHERE id = ?", (journal_id,), one=True)
    if not j or j["branch_id"] != branch_id or j["type"] not in ("bank", "cash") or not j["default_account_id"]:
        raise AccountingError(t("Choose a bank or cash journal of the branch."))
    return j


def pay(doc_table, doc_id, journal_id, d, user_id=None):
    """Pay the net of a posted payroll run or settlement: Dr Salaries Payable / Cr Bank."""
    doc = q(f"SELECT * FROM {doc_table} WHERE id = ?", (doc_id,), one=True)
    if doc["state"] != "posted":
        raise AccountingError(t("This action is not allowed in the current state."))
    j = pay_journal_account(doc["branch_id"], int(journal_id))
    st = _accounts(settings())
    net = run_totals(doc_id)["net"] if doc_table == "payroll_runs" else doc["net"]
    move_id = None
    if net:
        en = Entry(f"{t('Salary payment')} {doc['number']}")
        en.add(st["salary_payable_id"], net)
        en.add(j["default_account_id"], -net)
        move_id = create_move(doc["branch_id"], j["id"], d, en.lines(), user_id, ref=doc["number"],
                              narration=t("Salary payment"), post=True)
    x(f"UPDATE {doc_table} SET state = 'paid', pay_move_id = ?, pay_journal_id = ?, paid_date = ? WHERE id = ?",
      (move_id, j["id"], d, doc_id))
    audit(user_id, "pay", doc_table, doc_id, doc["number"])
    return move_id


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

def disburse_loan(loan_id, journal_id, user_id=None):
    ln = q("SELECT * FROM employee_loans WHERE id = ?", (loan_id,), one=True)
    if ln["state"] != "draft":
        raise AccountingError(t("This action is not allowed in the current state."))
    j = pay_journal_account(ln["branch_id"], int(journal_id))
    st = _accounts(settings())
    emp = q("SELECT * FROM employees WHERE id = ?", (ln["employee_id"],), one=True)
    en = Entry(f"{t('Employee loan')} {emp['code']} {nm(emp)}")
    en.add(st["loan_account_id"], ln["amount"])
    en.add(j["default_account_id"], -ln["amount"])
    move_id = create_move(ln["branch_id"], j["id"], ln["date"], en.lines(), user_id, ref=ln["number"],
                          narration=t("Employee loan"), post=True)
    x("UPDATE employee_loans SET state = 'running', journal_id = ?, move_id = ? WHERE id = ?", (j["id"], move_id, loan_id))
    audit(user_id, "post", "loan", loan_id, ln["number"])
    return move_id


# ---------------------------------------------------------------- final settlement

def compute_settlement(e, leave_date, reason, additions=0, deductions=0, st=None):
    st = st or settings()
    dec = branch_decimals(e["branch_id"])
    r = lambda v: round_cur(int(round(v)), dec)
    years, wage, full, due = eosb(e, leave_date, reason, st)
    days = leave_balance(e, leave_date)
    out = {"years": round(years, 4), "wage": wage, "eosb_full": r(full), "eosb_due": r(due), "leave_days": days,
           "leave_pay": r(days * leave_daily(e)), "loan_balance": loan_balance(e["id"]),
           "additions": additions, "deductions": deductions}
    out["net"] = out["eosb_due"] + out["leave_pay"] + additions - deductions - out["loan_balance"]
    return out


def post_settlement(sid, user_id=None):
    s = q("SELECT * FROM eos_settlements WHERE id = ?", (sid,), one=True)
    if s["state"] != "draft":
        raise AccountingError(t("This action is not allowed in the current state."))
    e = q("SELECT * FROM employees WHERE id = ?", (s["employee_id"],), one=True)
    if e["branch_id"] != s["branch_id"]:
        raise AccountingError(t("The employee has moved to another branch; create the settlement again."))
    vals = compute_settlement(e, s["leave_date"], s["reason"], s["additions"], s["deductions"])
    x(f"UPDATE eos_settlements SET {', '.join(k + ' = ?' for k in vals)} WHERE id = ?", list(vals.values()) + [sid])
    st = _accounts(settings())
    cc = e["cost_centre_id"]
    en = Entry(f"{t('Final settlement')} {e['code']} {nm(e)}")
    en.add(st["eosb_provision_id"], e["eosb_booked"])
    en.add(st["eosb_expense_id"], vals["eosb_due"] - e["eosb_booked"], cc)
    en.add(st["leave_provision_id"], e["leave_booked"])
    en.add(st["leave_expense_id"], vals["leave_pay"] - e["leave_booked"], cc)
    en.add(st["salary_expense_id"], vals["additions"] - vals["deductions"], cc)
    en.add(st["loan_account_id"], -vals["loan_balance"])
    en.add(st["salary_payable_id"], -vals["net"])
    move_id = None
    if en.lines():
        move_id = create_move(s["branch_id"], hr_journal(s["branch_id"]), s["leave_date"], en.lines(), user_id,
                              ref=s["number"], narration=t("Final settlement"), post=True)
    x("UPDATE eos_settlements SET state = ?, move_id = ? WHERE id = ?", ("posted" if vals["net"] else "paid", move_id, sid))
    x("UPDATE employees SET state = 'left', leave_date = ?, eosb_booked = 0, leave_booked = 0 WHERE id = ?",
      (s["leave_date"], e["id"]))
    close_repaid_loans(e["id"])
    audit(user_id, "post", "settlement", sid, s["number"])
    return move_id


# ---------------------------------------------------------------- alerts

def expiring_documents(branch_ids, days=60):
    """Active employees whose ID / Iqama or passport expires within `days` (or has expired)."""
    if not branch_ids:
        return []
    limit = (date.today() + timedelta(days=days)).isoformat()
    out = []
    for e in q(f"SELECT * FROM employees WHERE state = 'active' AND branch_id IN ({placeholders(branch_ids)}) "
               f"ORDER BY code", list(branch_ids)):
        for doc, col in (("ID / Iqama", "id_expiry"), ("Passport", "passport_expiry")):
            if e[col] and e[col] <= limit:
                out.append({"employee": e, "doc": doc, "expiry": e[col],
                            "expired": e[col] < date.today().isoformat()})
    return sorted(out, key=lambda r: r["expiry"])
