<?php
/**
 * Employee Dashboard API — returns all HRMS metrics as JSON
 * Called via AJAX from employee.php for non-blocking page load
 */
error_reporting(0);
$PageSecurity = 0;

// Buffer any output from session.inc (it may echo HTML/headers)
ob_start();
include('includes/session.inc');
ob_end_clean();

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

$conn = new mysqli('localhost', 'sooggoo', '4Compassion*', 'evergreenint');
if ($conn->connect_error) {
    echo json_encode(['error' => 'DB connection failed']);
    exit;
}
$conn->set_charset("utf8");

$curYear = date('Y');
$curMonth = (int)date('m');
$curDay = (int)date('d');

$out = [];

// ═══════════════════════════════════════════
// SINGLE combined KPI + FNPF query (was 3 separate queries)
// ═══════════════════════════════════════════
$r = $conn->query("SELECT 
    COUNT(*) as total,
    SUM(status='Active') as active,
    SUM(status='Terminated' OR status='InActive') as inactive,
    SUM(status='Final Warning') as warnings,
    SUM(YEAR(start_date)={$curYear} AND status='Active') as new_hires,
    SUM(YEAR(termination_date)={$curYear} AND (status='Terminated' OR status='InActive')) as exits_yr,
    ROUND(AVG(IF(status='Active' AND start_date>'0000-00-00', TIMESTAMPDIFF(MONTH,start_date,CURDATE())/12, NULL)),1) as avg_service,
    ROUND(AVG(IF(status='Active' AND date_of_birth>'0000-00-00', TIMESTAMPDIFF(YEAR,date_of_birth,CURDATE()), NULL)),1) as avg_age,
    SUM(status='Active' AND gender='Male') as male,
    SUM(status='Active' AND gender='Female') as female,
    SUM(status='Active' AND fnpf IS NOT NULL AND fnpf!='') as fnpf_yes,
    SUM(status='Active' AND (fnpf IS NULL OR fnpf='')) as fnpf_no,
    SUM(status='Active' AND (tin IS NULL OR tin='')) as no_tin,
    SUM(status='Active' AND (fnpf IS NULL OR fnpf='')) as no_fnpf,
    SUM(status='Active' AND (email_address IS NULL OR email_address='')) as no_email,
    SUM(status='Active' AND (photo IS NULL OR photo='')) as no_photo,
    SUM(status='Active' AND (mob IS NULL OR mob='')) as no_mobile,
    SUM(status='Active' AND (date_of_birth IS NULL OR date_of_birth='' OR date_of_birth='0000-00-00')) as no_dob,
    SUM(status='Active' AND (emergency_contact IS NULL OR emergency_contact='')) as no_emergency
FROM employeemaster");
$kpi = $r ? $r->fetch_assoc() : [];
$out['kpi'] = $kpi;

$active = max(1, (int)$kpi['active']);
$out['kpi']['fnpf_pct'] = round(((int)$kpi['fnpf_yes'] / $active) * 100);
$out['kpi']['turnover'] = round(((int)$kpi['exits_yr'] / $active) * 100, 1);
$out['kpi']['male_pct'] = round(((int)$kpi['male'] / $active) * 100);
$out['kpi']['female_pct'] = 100 - $out['kpi']['male_pct'];

// ═══════════════════════════════════════════
// Department headcount (active)
// ═══════════════════════════════════════════
$out['departments'] = [];
$r2 = $conn->query("SELECT IFNULL(d.departmentname,'Unassigned') as name, COUNT(*) as cnt 
    FROM employeemaster e LEFT JOIN department d ON e.department=d.departmentid 
    WHERE e.status='Active' GROUP BY e.department ORDER BY cnt DESC LIMIT 12");
if($r2) while($row=$r2->fetch_assoc()) $out['departments'][] = $row;

// ═══════════════════════════════════════════
// Birthdays this month
// ═══════════════════════════════════════════
$out['birthdays'] = [];
$r3 = $conn->query("SELECT employeeid, IFNULL(first_name,'') as first_name, IFNULL(last_name,'') as last_name, DAY(date_of_birth) as bday, 
    TIMESTAMPDIFF(YEAR,date_of_birth,CURDATE()) as age
    FROM employeemaster 
    WHERE status='Active' AND MONTH(date_of_birth)={$curMonth} AND date_of_birth>'0000-00-00'
    ORDER BY DAY(date_of_birth)");
if($r3) while($row=$r3->fetch_assoc()) $out['birthdays'][] = $row;

// ═══════════════════════════════════════════
// Security guard licence status (count all, return top 20 each for display)
// ═══════════════════════════════════════════
$out['guards_licensed'] = [];
$out['guards_unlicensed'] = [];
$guardCountR = $conn->query("SELECT 
    SUM(security_licence IS NOT NULL AND TRIM(security_licence)!='') as lic,
    SUM(security_licence IS NULL OR TRIM(security_licence)='') as unlic
    FROM employeemaster 
    WHERE status='Active' AND (job_role LIKE '%Security Officer%' OR job_role LIKE '%Security Supervisor%')");
$gc = $guardCountR ? $guardCountR->fetch_assoc() : ['lic'=>0,'unlic'=>0];
$out['guard_lic_count'] = (int)$gc['lic'];
$out['guard_unlic_count'] = (int)$gc['unlic'];
$out['guard_total'] = (int)$gc['lic'] + (int)$gc['unlic'];
$out['guard_lic_pct'] = ($out['guard_total'] > 0) ? round(((int)$gc['lic'] / $out['guard_total']) * 100) : 0;

$r4a = $conn->query("SELECT employeeid, IFNULL(first_name,'') as first_name, IFNULL(last_name,'') as last_name, IFNULL(job_role,'') as job_role, IFNULL(security_licence,'') as security_licence
    FROM employeemaster 
    WHERE status='Active' AND (job_role LIKE '%Security Officer%' OR job_role LIKE '%Security Supervisor%')
    AND security_licence IS NOT NULL AND TRIM(security_licence)!=''
    ORDER BY first_name LIMIT 20");
if($r4a) while($row=$r4a->fetch_assoc()) $out['guards_licensed'][] = $row;

$r4b = $conn->query("SELECT employeeid, IFNULL(first_name,'') as first_name, IFNULL(last_name,'') as last_name, IFNULL(job_role,'') as job_role
    FROM employeemaster 
    WHERE status='Active' AND (job_role LIKE '%Security Officer%' OR job_role LIKE '%Security Supervisor%')
    AND (security_licence IS NULL OR TRIM(security_licence)='')
    ORDER BY first_name LIMIT 20");
if($r4b) while($row=$r4b->fetch_assoc()) $out['guards_unlicensed'][] = $row;

// ═══════════════════════════════════════════
// Employment type + Tenure in ONE query (was 2 separate)
// ═══════════════════════════════════════════
$out['emp_types'] = [];
$r5 = $conn->query("SELECT 
    CASE WHEN job_type IN('Full-time','Full-Time') THEN 'Full-Time'
         WHEN job_type IN('Part-Time','Part-time') THEN 'Part-Time'
         WHEN job_type='Casual' THEN 'Casual' ELSE 'Other' END as jtype,
    COUNT(*) as cnt
    FROM employeemaster WHERE status='Active' GROUP BY jtype ORDER BY cnt DESC");
if($r5) while($row=$r5->fetch_assoc()) $out['emp_types'][] = $row;

$out['tenure'] = ['under1'=>0,'1to3'=>0,'3to5'=>0,'5to10'=>0,'over10'=>0];
$r6 = $conn->query("SELECT 
    CASE 
        WHEN TIMESTAMPDIFF(MONTH,start_date,CURDATE())/12<1 THEN 'under1'
        WHEN TIMESTAMPDIFF(MONTH,start_date,CURDATE())/12<3 THEN '1to3'
        WHEN TIMESTAMPDIFF(MONTH,start_date,CURDATE())/12<5 THEN '3to5'
        WHEN TIMESTAMPDIFF(MONTH,start_date,CURDATE())/12<10 THEN '5to10'
        ELSE 'over10' END as bracket, COUNT(*) as cnt
    FROM employeemaster WHERE status='Active' AND start_date>'0000-00-00' GROUP BY bracket");
if($r6) while($row=$r6->fetch_assoc()) $out['tenure'][$row['bracket']] = (int)$row['cnt'];

// ═══════════════════════════════════════════
// Location / Suburb
// ═══════════════════════════════════════════
$out['locations'] = [];
$r7 = $conn->query("SELECT suburb as name, COUNT(*) as cnt FROM employeemaster 
    WHERE status='Active' AND suburb IS NOT NULL AND suburb!='' 
    GROUP BY suburb ORDER BY cnt DESC LIMIT 8");
if($r7) while($row=$r7->fetch_assoc()) $out['locations'][] = $row;

// ═══════════════════════════════════════════
// Recent hires (last 8)
// ═══════════════════════════════════════════
$out['recent_hires'] = [];
$r8 = $conn->query("SELECT e.employeeid, IFNULL(e.first_name,'') as first_name, IFNULL(e.last_name,'') as last_name, e.start_date, IFNULL(e.job_role,'') as job_role,
    IFNULL(d.departmentname,'') as dept, DATEDIFF(CURDATE(),e.start_date) as days_ago
    FROM employeemaster e LEFT JOIN department d ON e.department=d.departmentid
    WHERE e.status='Active' AND e.start_date>'0000-00-00'
    ORDER BY e.start_date DESC LIMIT 8");
if($r8) while($row=$r8->fetch_assoc()) $out['recent_hires'][] = $row;

// ═══════════════════════════════════════════
// Long service milestones (5/10/15/20/25/30 yrs this year)
// ═══════════════════════════════════════════
$out['milestones'] = [];
$r9 = $conn->query("SELECT employeeid, IFNULL(first_name,'') as first_name, IFNULL(last_name,'') as last_name, start_date, IFNULL(job_role,'') as job_role,
    FLOOR(TIMESTAMPDIFF(MONTH,start_date,'{$curYear}-12-31')/12) as yrs
    FROM employeemaster 
    WHERE status='Active' AND start_date>'0000-00-00'
    HAVING yrs IN(5,10,15,20,25,30)
    ORDER BY yrs DESC, start_date");
if($r9) while($row=$r9->fetch_assoc()) $out['milestones'][] = $row;

$out['month_name'] = date('F');
$out['cur_day'] = $curDay;
$out['cur_year'] = $curYear;

$conn->close();
echo json_encode($out);
die();
