<?php
/* PaidEmployeeTimesheetReportAPI.php - Dashboard data API */
error_reporting(E_ALL & ~E_NOTICE);
ob_start();
$PageSecurity = 0;
include('includes/session.inc');
ob_end_clean();

header('Content-Type: application/json');

$host = 'localhost';
$dbuser = 'sooggoo';
$dbpass = '4Compassion*';
$dbname = 'evergreenint';

$conn = new mysqli($host, $dbuser, $dbpass, $dbname);
if ($conn->connect_error) {
    echo json_encode(array('error' => 'DB connection failed'));
    exit;
}
$conn->set_charset('utf8');

$action = isset($_GET['action']) ? $_GET['action'] : '';

if ($action == 'report_summary') {
    // Summary KPIs for the report that was just generated
    $fromdate = isset($_GET['fromdate']) ? $conn->real_escape_string($_GET['fromdate']) : '';
    $todate = isset($_GET['todate']) ? $conn->real_escape_string($_GET['todate']) : '';
    $department = isset($_GET['department']) ? $conn->real_escape_string($_GET['department']) : '';

    $data = array();

    $where = "payrun_details.id BETWEEN '$fromdate' AND '$todate' AND payrun_details.department = '$department'";

    // Totals
    $sql = "SELECT COUNT(DISTINCT employeeid) as total_employees,
                   COALESCE(SUM(normal_hours),0) as total_normal_hours,
                   COALESCE(SUM(normal_pay),0) as total_normal_pay,
                   COALESCE(SUM(timehalf_hours),0) as total_timehalf_hours,
                   COALESCE(SUM(timehalf_amount),0) as total_timehalf_amount,
                   COALESCE(SUM(double_hours),0) as total_double_hours,
                   COALESCE(SUM(double_amount),0) as total_double_amount,
                   COALESCE(SUM(premium),0) as total_premium,
                   COALESCE(SUM(stay_away),0) as total_stayaway,
                   COALESCE(SUM(call_out_allowances),0) as total_callout,
                   COALESCE(SUM(leave_allowances),0) as total_leave_allow,
                   COALESCE(SUM(leave_allowances),0) as total_sickleave,
                   COALESCE(SUM(annual_leave),0) as total_annual,
                   COALESCE(SUM(maternity_leave),0) as total_maternity,
                   COALESCE(SUM(other_allowances_fnpf),0) as total_other_fnpf,
                   COALESCE(SUM(other_allowances_nonfnpf),0) as total_other_nonfnpf,
                   COALESCE(SUM(meal_allowance),0) as total_meal,
                   COALESCE(SUM(totalgross_amount),0) as total_gross,
                   COALESCE(SUM(employee_fnpf),0) as total_emp_fnpf,
                   COALESCE(SUM(fixed_deductions),0) as total_fixed_ded,
                   COALESCE(SUM(net_pay),0) as total_net_pay
            FROM payrun_details WHERE $where";
    $r = $conn->query($sql);
    if ($r && $row = $r->fetch_assoc()) {
        foreach ($row as $k => $v) {
            $data[$k] = is_numeric($v) ? round((float)$v, 2) : $v;
        }
    }

    // Top 5 earners
    $sql = "SELECT employeeid, full_name,
                   COALESCE(SUM(totalgross_amount),0) as gross,
                   COALESCE(SUM(net_pay),0) as net,
                   COALESCE(SUM(normal_hours + timehalf_hours + double_hours),0) as total_hours
            FROM payrun_details WHERE $where
            GROUP BY employeeid, full_name ORDER BY gross DESC LIMIT 5";
    $r = $conn->query($sql);
    $data['top_earners'] = array();
    if ($r) {
        while ($row = $r->fetch_assoc()) {
            $data['top_earners'][] = array(
                'id' => $row['employeeid'],
                'name' => isset($row['full_name']) ? $row['full_name'] : $row['employeeid'],
                'gross' => round((float)$row['gross'], 2),
                'net' => round((float)$row['net'], 2),
                'hours' => round((float)$row['total_hours'], 2)
            );
        }
    }

    // Pay composition breakdown
    $data['pay_composition'] = array(
        array('category' => 'Normal Pay', 'amount' => isset($data['total_normal_pay']) ? $data['total_normal_pay'] : 0),
        array('category' => 'Time & Half', 'amount' => isset($data['total_timehalf_amount']) ? $data['total_timehalf_amount'] : 0),
        array('category' => 'Double Pay', 'amount' => isset($data['total_double_amount']) ? $data['total_double_amount'] : 0),
        array('category' => 'Premium', 'amount' => isset($data['total_premium']) ? $data['total_premium'] : 0),
        array('category' => 'Stay Away', 'amount' => isset($data['total_stayaway']) ? $data['total_stayaway'] : 0),
        array('category' => 'Call Out', 'amount' => isset($data['total_callout']) ? $data['total_callout'] : 0),
        array('category' => 'Leave Allow.', 'amount' => isset($data['total_leave_allow']) ? $data['total_leave_allow'] : 0),
        array('category' => 'Sick Leave', 'amount' => isset($data['total_sickleave']) ? $data['total_sickleave'] : 0),
        array('category' => 'Annual Leave', 'amount' => isset($data['total_annual']) ? $data['total_annual'] : 0),
        array('category' => 'Maternity', 'amount' => isset($data['total_maternity']) ? $data['total_maternity'] : 0),
        array('category' => 'Other FNPF', 'amount' => isset($data['total_other_fnpf']) ? $data['total_other_fnpf'] : 0),
        array('category' => 'Other Non-FNPF', 'amount' => isset($data['total_other_nonfnpf']) ? $data['total_other_nonfnpf'] : 0),
        array('category' => 'Meal Allow.', 'amount' => isset($data['total_meal']) ? $data['total_meal'] : 0)
    );
    // Filter out zero values
    $filtered = array();
    foreach ($data['pay_composition'] as $item) {
        if ($item['amount'] > 0) $filtered[] = $item;
    }
    $data['pay_composition'] = $filtered;

    // Department name
    $sql = "SELECT departmentname FROM department WHERE departmentid = '$department' LIMIT 1";
    $r = $conn->query($sql);
    $data['department_name'] = ($r && $row = $r->fetch_assoc()) ? $row['departmentname'] : 'Dept #'.$department;

    echo json_encode($data);
}

if ($action == 'ytd_dashboard') {
    $currentYear = date('Y');
    $data = array();

    // YTD totals
    $sql = "SELECT COUNT(DISTINCT employeeid) as total_employees,
                   COALESCE(SUM(totalgross_amount),0) as total_gross,
                   COALESCE(SUM(net_pay),0) as total_net,
                   COALESCE(SUM(normal_hours),0) as total_normal_hours,
                   COALESCE(SUM(employee_fnpf),0) as total_fnpf,
                   COALESCE(SUM(fixed_deductions),0) as total_deductions,
                   COUNT(*) as total_records
            FROM payrun_details pd
            INNER JOIN calendar_management cm ON pd.id = cm.id
            WHERE YEAR(cm.date_created) = '$currentYear'";
    $r = $conn->query($sql);
    if ($r && $row = $r->fetch_assoc()) {
        foreach ($row as $k => $v) {
            $data[$k] = is_numeric($v) ? round((float)$v, 2) : $v;
        }
    }

    // Monthly gross trend
    $sql = "SELECT MONTH(cm.date_created) as m,
                   COALESCE(SUM(pd.totalgross_amount),0) as gross,
                   COALESCE(SUM(pd.net_pay),0) as net,
                   COUNT(DISTINCT pd.employeeid) as emps
            FROM payrun_details pd
            INNER JOIN calendar_management cm ON pd.id = cm.id
            WHERE YEAR(cm.date_created) = '$currentYear'
            GROUP BY MONTH(cm.date_created) ORDER BY m";
    $r = $conn->query($sql);
    $months = array('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec');
    $monthly = array();
    for ($i = 1; $i <= 12; $i++) {
        $monthly[$i] = array('month' => $months[$i-1], 'gross' => 0, 'net' => 0, 'emps' => 0);
    }
    if ($r) {
        while ($row = $r->fetch_assoc()) {
            $m = (int)$row['m'];
            $monthly[$m]['gross'] = round((float)$row['gross'], 2);
            $monthly[$m]['net'] = round((float)$row['net'], 2);
            $monthly[$m]['emps'] = (int)$row['emps'];
        }
    }
    $data['monthly_trend'] = array_values($monthly);

    // Top departments
    $sql = "SELECT d.departmentname, COALESCE(SUM(pd.totalgross_amount),0) as gross,
                   COUNT(DISTINCT pd.employeeid) as emps
            FROM payrun_details pd
            INNER JOIN calendar_management cm ON pd.id = cm.id
            LEFT JOIN department d ON pd.department = d.departmentid
            WHERE YEAR(cm.date_created) = '$currentYear'
            GROUP BY pd.department, d.departmentname ORDER BY gross DESC LIMIT 8";
    $r = $conn->query($sql);
    $data['top_departments'] = array();
    if ($r) {
        while ($row = $r->fetch_assoc()) {
            $data['top_departments'][] = array(
                'name' => isset($row['departmentname']) ? $row['departmentname'] : 'Unknown',
                'gross' => round((float)$row['gross'], 2),
                'emps' => (int)$row['emps']
            );
        }
    }

    $data['current_year'] = $currentYear;
    echo json_encode($data);
}

$conn->close();
?>
