<?php

require 'config.php';
session_start();
session_write_close();

if(!isset($_SESSION['user_id'])){
    header("Location: login.php");
    exit;
}

$role = strtolower($_SESSION['role']);

if(
    !in_array(
        $role,
        [
            'accounts',
            'gm',
            'audit',
            'admin',
            'superadmin'
        ]
    )
){
    die("Access Denied");
}

$month = intval($_GET['month'] ?? date('m'));
$year  = intval($_GET['year'] ?? date('Y'));

$departmentReport = $conn->query("

SELECT

d.name department,

COUNT(*) employees,

SUM(p.gross_salary) gross_salary,

SUM(p.net_salary) net_salary,

SUM(p.tax_amount) tax,

SUM(p.pension_amount) pension,

SUM(p.loan_deduction) loans,

SUM(p.salary_advance_deduction) advances,

SUM(p.overtime_pay) overtime

FROM payroll_details p

INNER JOIN employees e
ON e.id=p.employee_id

LEFT JOIN departments d
ON d.id=e.department_id

WHERE p.payroll_month=$month
AND p.payroll_year=$year

GROUP BY d.id

ORDER BY d.name

");

$summary = $conn->query("

SELECT

SUM(gross_salary) gross_salary,
SUM(net_salary) net_salary,
SUM(tax_amount) tax_amount,
SUM(pension_amount) pension_amount,
SUM(loan_deduction) loan_deduction,
SUM(salary_advance_deduction) salary_advance_deduction,
SUM(overtime_pay) overtime_pay

FROM payroll_details

WHERE payroll_month=$month
AND payroll_year=$year

")->fetch_assoc();

?>

<!DOCTYPE html>
<html>
<head>

<title>Finance Reports</title>

<script src="https://cdn.tailwindcss.com"></script>

</head>

<body class="flex bg-gray-100">

<?php include 'sidebar.php'; ?>

<div class="flex-1 p-8" id="main-content">

<h1 class="text-3xl font-bold mb-6">
Finance Reports
</h1>

<form method="GET"
class="flex gap-4 mb-6">

<select
name="month"
class="border p-3 rounded">

<?php for($m=1;$m<=12;$m++): ?>

<option
value="<?= $m ?>"
<?= $month==$m?'selected':'' ?>>

<?= date('F',mktime(0,0,0,$m,1)) ?>

</option>

<?php endfor; ?>

</select>

<input
type="number"
name="year"
value="<?= $year ?>"
class="border p-3 rounded w-32">

<button
class="bg-blue-600 text-white px-5 rounded">

Load

</button>

</form>

<div class="grid md:grid-cols-3 gap-5 mb-8">

<div class="bg-white p-5 rounded-xl shadow">
<div class="text-gray-500">Gross Payroll</div>
<div class="text-2xl font-bold text-blue-700">
₦<?= number_format($summary['gross_salary'],2) ?>
</div>
</div>

<div class="bg-white p-5 rounded-xl shadow">
<div class="text-gray-500">Net Payroll</div>
<div class="text-2xl font-bold text-green-700">
₦<?= number_format($summary['net_salary'],2) ?>
</div>
</div>

<div class="bg-white p-5 rounded-xl shadow">
<div class="text-gray-500">PAYE</div>
<div class="text-2xl font-bold text-red-700">
₦<?= number_format($summary['tax_amount'],2) ?>
</div>
</div>

<div class="bg-white p-5 rounded-xl shadow">
<div class="text-gray-500">Pension</div>
<div class="text-2xl font-bold text-yellow-700">
₦<?= number_format($summary['pension_amount'],2) ?>
</div>
</div>

<div class="bg-white p-5 rounded-xl shadow">
<div class="text-gray-500">Loans Recovered</div>
<div class="text-2xl font-bold text-red-700">
₦<?= number_format($summary['loan_deduction'],2) ?>
</div>
</div>

<div class="bg-white p-5 rounded-xl shadow">
<div class="text-gray-500">Salary Advances</div>
<div class="text-2xl font-bold text-red-700">
₦<?= number_format($summary['salary_advance_deduction'],2) ?>
</div>
</div>

</div>

<div class="bg-white rounded-xl shadow overflow-hidden">

<div class="p-5 border-b">

<h2 class="font-bold text-lg">
Department Payroll Cost
</h2>

</div>

<table class="w-full">

<thead class="bg-gray-100">

<tr>

<th class="p-4 text-left">Department</th>
<th class="p-4">Employees</th>
<th class="p-4">Gross</th>
<th class="p-4">Net</th>
<th class="p-4">PAYE</th>
<th class="p-4">Pension</th>
<th class="p-4">Loans</th>
<th class="p-4">Advances</th>
<th class="p-4">Overtime</th>

</tr>

</thead>

<tbody>

<?php while($row=$departmentReport->fetch_assoc()): ?>

<tr class="border-b">

<td class="p-4">
<?= htmlspecialchars($row['department']) ?>
</td>

<td class="p-4 text-center">
<?= number_format($row['employees']) ?>
</td>

<td class="p-4 text-right">
₦<?= number_format($row['gross_salary'],2) ?>
</td>

<td class="p-4 text-right">
₦<?= number_format($row['net_salary'],2) ?>
</td>

<td class="p-4 text-right">
₦<?= number_format($row['tax'],2) ?>
</td>

<td class="p-4 text-right">
₦<?= number_format($row['pension'],2) ?>
</td>

<td class="p-4 text-right">
₦<?= number_format($row['loans'],2) ?>
</td>

<td class="p-4 text-right">
₦<?= number_format($row['advances'],2) ?>
</td>

<td class="p-4 text-right">
₦<?= number_format($row['overtime'],2) ?>
</td>

</tr>

<?php endwhile; ?>

</tbody>

</table>

</div>

</div>

</body>
</html>