<?php
require_once __DIR__ . '/../config/db.php';
require_once __DIR__ . '/../config/auth.php';
$user = requireRole([1, 5]);
$db   = getDB();
$year = (int)post('year', date('Y'));

// Summary per employee per leave type
$stmt = $db->prepare("
    SELECT e.id, CONCAT(e.first_name,' ',e.last_name) AS employee_name, e.employee_number, e.department,
           el.leave_type,
           SUM(CASE WHEN el.status='approved' THEN el.days ELSE 0 END) AS approved,
           SUM(CASE WHEN el.status='pending' THEN el.days ELSE 0 END) AS pending,
           SUM(CASE WHEN el.status='rejected' THEN el.days ELSE 0 END) AS rejected,
           elb.allocated, elb.used, elb.carried_over,
           (COALESCE(elb.allocated,0)+COALESCE(elb.carried_over,0)-COALESCE(elb.used,0)) AS remaining
    FROM employees e
    LEFT JOIN employee_leave el ON el.employee_id=e.id AND YEAR(el.start_date)=?
    LEFT JOIN employee_leave_balance elb ON elb.employee_id=e.id AND elb.year=? AND elb.leave_type=el.leave_type
    WHERE e.status='active'
    GROUP BY e.id, el.leave_type, elb.allocated, elb.used, elb.carried_over
    ORDER BY e.last_name, e.first_name, el.leave_type
");
$stmt->execute([$year, $year]);
apiSuccess(['report' => $stmt->fetchAll(), 'year' => $year]);