<?php
/* ManualTimesheetAPI.php - Dashboard data API for Manual Timesheet */
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'] : '';
$currentYear = date('Y');

if ($action == 'dashboard') {
    $data = array();

    // --- KPI 1: Total Calendar Periods This Year ---
    $sql = "SELECT COUNT(*) as total FROM calendar_management WHERE YEAR(date_created) = '$currentYear'";
    $r = $conn->query($sql);
    $data['total_periods'] = ($r && $row = $r->fetch_assoc()) ? (int)$row['total'] : 0;

    // --- KPI 2: Total Timesheet Entries This Year ---
    $sql = "SELECT COUNT(*) as total FROM manual_timesheet mt
            INNER JOIN calendar_management cm ON mt.id = cm.id
            WHERE YEAR(cm.date_created) = '$currentYear'";
    $r = $conn->query($sql);
    $data['total_entries'] = ($r && $row = $r->fetch_assoc()) ? (int)$row['total'] : 0;

    // --- KPI 3: Total Gross This Year ---
    $sql = "SELECT COALESCE(SUM(mt.gross), 0) as total_gross FROM manual_timesheet mt
            INNER JOIN calendar_management cm ON mt.id = cm.id
            WHERE YEAR(cm.date_created) = '$currentYear'";
    $r = $conn->query($sql);
    $data['total_gross'] = ($r && $row = $r->fetch_assoc()) ? round((float)$row['total_gross'], 2) : 0;

    // --- KPI 4: Total Hours This Year ---
    $sql = "SELECT COALESCE(SUM(mt.hours), 0) as total_hours FROM manual_timesheet mt
            INNER JOIN calendar_management cm ON mt.id = cm.id
            WHERE YEAR(cm.date_created) = '$currentYear'";
    $r = $conn->query($sql);
    $data['total_hours'] = ($r && $row = $r->fetch_assoc()) ? round((float)$row['total_hours'], 2) : 0;

    // --- KPI 5: Approved Entries ---
    $sql = "SELECT COUNT(*) as total FROM manual_timesheet mt
            INNER JOIN calendar_management cm ON mt.id = cm.id
            WHERE YEAR(cm.date_created) = '$currentYear' AND mt.approved = 'yes'";
    $r = $conn->query($sql);
    $data['approved_entries'] = ($r && $row = $r->fetch_assoc()) ? (int)$row['total'] : 0;

    // --- KPI 6: Pending Approval ---
    $sql = "SELECT COUNT(*) as total FROM manual_timesheet mt
            INNER JOIN calendar_management cm ON mt.id = cm.id
            WHERE YEAR(cm.date_created) = '$currentYear' AND (mt.approved IS NULL OR mt.approved = '' OR mt.approved = 'no')";
    $r = $conn->query($sql);
    $data['pending_entries'] = ($r && $row = $r->fetch_assoc()) ? (int)$row['total'] : 0;

    // --- Calendar Status Distribution ---
    $sql = "SELECT COALESCE(status, 'unknown') as status, COUNT(*) as cnt 
            FROM calendar_management 
            WHERE YEAR(date_created) = '$currentYear' 
            GROUP BY status ORDER BY cnt DESC";
    $r = $conn->query($sql);
    $data['status_distribution'] = array();
    if ($r) {
        while ($row = $r->fetch_assoc()) {
            $data['status_distribution'][] = array(
                'status' => ucfirst($row['status']),
                'count' => (int)$row['cnt']
            );
        }
    }

    // --- Leave Type Breakdown ---
    $sql = "SELECT mt.leave_type, COUNT(*) as cnt, COALESCE(SUM(mt.hours), 0) as total_hours
            FROM manual_timesheet mt
            INNER JOIN calendar_management cm ON mt.id = cm.id
            WHERE YEAR(cm.date_created) = '$currentYear' AND mt.leave_type IS NOT NULL AND mt.leave_type != '' AND mt.leave_type != 'none'
            GROUP BY mt.leave_type ORDER BY cnt DESC";
    $r = $conn->query($sql);
    $data['leave_breakdown'] = array();
    if ($r) {
        while ($row = $r->fetch_assoc()) {
            $data['leave_breakdown'][] = array(
                'type' => $row['leave_type'],
                'count' => (int)$row['cnt'],
                'hours' => round((float)$row['total_hours'], 2)
            );
        }
    }

    // --- Department Breakdown (Top 10) ---
    $sql = "SELECT d.departmentname, COUNT(*) as cnt, COALESCE(SUM(mt.gross), 0) as total_gross, COALESCE(SUM(mt.hours), 0) as total_hours
            FROM manual_timesheet mt
            INNER JOIN calendar_management cm ON mt.id = cm.id
            LEFT JOIN department d ON mt.department = d.departmentid
            WHERE YEAR(cm.date_created) = '$currentYear'
            GROUP BY mt.department, d.departmentname ORDER BY total_gross DESC LIMIT 10";
    $r = $conn->query($sql);
    $data['department_breakdown'] = array();
    if ($r) {
        while ($row = $r->fetch_assoc()) {
            $dept = isset($row['departmentname']) ? $row['departmentname'] : 'Unknown';
            $data['department_breakdown'][] = array(
                'department' => $dept,
                'count' => (int)$row['cnt'],
                'gross' => round((float)$row['total_gross'], 2),
                'hours' => round((float)$row['total_hours'], 2)
            );
        }
    }

    // --- Monthly Trend (Gross by Month This Year) ---
    $sql = "SELECT MONTH(cm.date_created) as m, COALESCE(SUM(mt.gross), 0) as total_gross, COUNT(*) as cnt
            FROM manual_timesheet mt
            INNER JOIN calendar_management cm ON mt.id = cm.id
            WHERE YEAR(cm.date_created) = '$currentYear'
            GROUP BY MONTH(cm.date_created) ORDER BY m ASC";
    $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, 'count' => 0);
    }
    if ($r) {
        while ($row = $r->fetch_assoc()) {
            $m = (int)$row['m'];
            $monthly[$m]['gross'] = round((float)$row['total_gross'], 2);
            $monthly[$m]['count'] = (int)$row['cnt'];
        }
    }
    $data['monthly_trend'] = array_values($monthly);

    // --- Recent Entries (Last 8) ---
    $sql = "SELECT mt.timesheetid, CONCAT(e.first_name, ' ', e.last_name) as employee_name,
            d.departmentname, mt.hours, mt.rate, mt.gross, mt.leave_type, mt.approved,
            mt.approved_by, mt.approved_date
            FROM manual_timesheet mt
            INNER JOIN calendar_management cm ON mt.id = cm.id
            LEFT JOIN employeemaster e ON mt.employeeid = e.employeeid
            LEFT JOIN department d ON mt.department = d.departmentid
            WHERE YEAR(cm.date_created) = '$currentYear'
            ORDER BY mt.timesheetid DESC LIMIT 8";
    $r = $conn->query($sql);
    $data['recent_entries'] = array();
    if ($r) {
        while ($row = $r->fetch_assoc()) {
            $data['recent_entries'][] = $row;
        }
    }

    // --- Top Employees by Gross (Top 8) ---
    $sql = "SELECT mt.employeeid, CONCAT(e.first_name, ' ', e.last_name) as employee_name,
            COALESCE(SUM(mt.gross), 0) as total_gross, COALESCE(SUM(mt.hours), 0) as total_hours, COUNT(*) as entries
            FROM manual_timesheet mt
            INNER JOIN calendar_management cm ON mt.id = cm.id
            LEFT JOIN employeemaster e ON mt.employeeid = e.employeeid
            WHERE YEAR(cm.date_created) = '$currentYear'
            GROUP BY mt.employeeid, e.first_name, e.last_name
            ORDER BY total_gross DESC LIMIT 8";
    $r = $conn->query($sql);
    $data['top_employees'] = array();
    if ($r) {
        while ($row = $r->fetch_assoc()) {
            $data['top_employees'][] = array(
                'employeeid' => $row['employeeid'],
                'name' => isset($row['employee_name']) ? $row['employee_name'] : 'Unknown',
                'gross' => round((float)$row['total_gross'], 2),
                'hours' => round((float)$row['total_hours'], 2),
                'entries' => (int)$row['entries']
            );
        }
    }

    $data['current_year'] = $currentYear;
    $data['current_user'] = isset($_SESSION['UserID']) ? $_SESSION['UserID'] : 'unknown';

    echo json_encode($data);
}

$conn->close();
?>
