<?php
/* ============================================================================
 *  config/loans.php — helpers for staff loans / borrowed money.
 *  A loan deducts a fixed monthly amount from salary each cycle until cleared.
 * ==========================================================================*/

/* Total still owed across a staff member's active (approved) loans. */
function loan_outstanding(PDO $pdo, int $companyId, int $staffId): float {
    try {
        $st = $pdo->prepare("SELECT COALESCE(SUM(remaining),0) FROM hr_loans
                             WHERE company_id=? AND staff_id=? AND status='approved' AND remaining>0");
        $st->execute([$companyId, $staffId]);
        return (float)$st->fetchColumn();
    } catch (Throwable $e) { return 0; }
}

/* The borrow deduction for a given cycle period (read-only — for display).
   Uses the already-recorded payment if one exists for this period, otherwise
   the scheduled monthly amount (capped at the remaining balance). */
function loan_period_deduction(PDO $pdo, int $companyId, int $staffId, string $periodStart): float {
    $total = 0.0;
    try {
        $st = $pdo->prepare("SELECT id, monthly_payment, remaining FROM hr_loans
                             WHERE company_id=? AND staff_id=? AND status='approved' AND remaining>0");
        $st->execute([$companyId, $staffId]);
        foreach ($st->fetchAll(PDO::FETCH_ASSOC) as $l) {
            $p = $pdo->prepare("SELECT amount FROM hr_loan_payments WHERE loan_id=? AND period_start=? LIMIT 1");
            $p->execute([$l['id'], $periodStart]);
            $rec = $p->fetchColumn();
            if ($rec !== false && $rec !== null) $total += (float)$rec;
            else $total += min((float)$l['monthly_payment'], (float)$l['remaining']);
        }
    } catch (Throwable $e) {}
    return round($total, 2);
}

/* Record this cycle's loan payments (idempotent — once per loan per period),
   reduce the remaining balance, and mark a loan completed when cleared.
   Returns the total deducted for the staff this period. Call on payroll save. */
function loan_record_payments(PDO $pdo, int $companyId, int $staffId, string $periodStart): float {
    $total = 0.0;
    try {
        $st = $pdo->prepare("SELECT id, monthly_payment, remaining FROM hr_loans
                             WHERE company_id=? AND staff_id=? AND status='approved' AND remaining>0");
        $st->execute([$companyId, $staffId]);
        foreach ($st->fetchAll(PDO::FETCH_ASSOC) as $l) {
            /* already recorded this period? add its amount and skip */
            $chk = $pdo->prepare("SELECT amount FROM hr_loan_payments WHERE loan_id=? AND period_start=? LIMIT 1");
            $chk->execute([$l['id'], $periodStart]);
            $existing = $chk->fetchColumn();
            if ($existing !== false && $existing !== null) { $total += (float)$existing; continue; }

            $pay = min((float)$l['monthly_payment'], (float)$l['remaining']);
            if ($pay <= 0) continue;

            $pdo->prepare("INSERT INTO hr_loan_payments (loan_id, company_id, staff_id, period_start, amount, created_at)
                           VALUES (?,?,?,?,?,NOW())")
                ->execute([$l['id'], $companyId, $staffId, $periodStart, $pay]);

            $newRem    = (float)$l['remaining'] - $pay;
            $newStatus = ($newRem <= 0.009) ? 'completed' : 'approved';
            $pdo->prepare("UPDATE hr_loans SET remaining=?, status=? WHERE id=?")
                ->execute([max(0, $newRem), $newStatus, $l['id']]);
            $total += $pay;
        }
    } catch (Throwable $e) {}
    return round($total, 2);
}