<?php
// Your database connection code here
include_once 'shikanisha.kts.php';

$sql = "SELECT employees.name,employees.employee_id, employees.phone_number, employees.salary_amount, 
        employees.registration_date
        FROM employees";

$result = mysqli_query($conn, $sql);

$response = array();

if (mysqli_num_rows($result) > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
        // Query to get amount paid for employee
        $sql2 = "SELECT SUM(salary) AS total_paid FROM employee_salary 
                WHERE phone_number = '" . $row["phone_number"] . "'";
        $result2 = mysqli_query($conn, $sql2);
        $row2 = mysqli_fetch_assoc($result2);

        // Calculate expected salary based on the current salary and duration
        $currentSalary = $row["salary_amount"];
        $registrationDate = $row["registration_date"];
        $durationMonths = floor((strtotime("now") - strtotime($registrationDate)) / (30 * 24 * 60 * 60));
        $expectedSalary = $currentSalary * $durationMonths;

        // Calculate balance salary
        $balance_salary = $expectedSalary - $row2["total_paid"];

        // Determine payment status
        if ($balance_salary < 0) {
            $status = 'Overpaid';
        } elseif ($balance_salary == 0) {
            $status = 'Paid';
        } elseif ($balance_salary > 0) {
            $status = 'Pending';
        }

        // Build the response array
        $response[] = array(
            'name' => $row['name'],
            'phone_number' => $row['phone_number'],
            'salary_amount' => $row['salary_amount'],
            'amount_paid' => $row2['total_paid'],
            'balance' => $balance_salary,
            'employee_id' =>$row['employee_id'],
            'status' => $status
        );
    }
}

// Return the response as JSON
header('Content-type: application/json');
echo json_encode($response);
?>
