<?php
/**
 * Enterprise HRM ERP System - Employee Reports
 */

require_once __DIR__ . '/../../includes/init.php';
requireLogin();

$pageTitle = 'Employee Reports';
$currentModule = 'reports';

$siteId = getCurrentSiteId();
$departmentId = isset($_GET['department_id']) ? (int)$_GET['department_id'] : 0;
$designationId = isset($_GET['designation_id']) ? (int)$_GET['designation_id'] : 0;

try {
    $db = getDB();
    
    // Get departments and designations for filter
    $stmt = $db->prepare("SELECT department_id, department_name FROM departments WHERE site_id = ? ORDER BY department_name");
    $stmt->execute([$siteId]);
    $departments = $stmt->fetchAll();
    
    $stmt = $db->prepare("SELECT designation_id, designation_name FROM designations WHERE site_id = ? ORDER BY designation_name");
    $stmt->execute([$siteId]);
    $designations = $stmt->fetchAll();
    
    // Overall Statistics
    $stats = [];
    
    // Build where clause
    $where = "e.site_id = ?";
    $params = [$siteId];
    
    if ($departmentId > 0) {
        $where .= " AND e.department_id = ?";
        $params[] = $departmentId;
    }
    
    if ($designationId > 0) {
        $where .= " AND e.designation_id = ?";
        $params[] = $designationId;
    }
    
    // Total Employees
    $stmt = $db->prepare("SELECT COUNT(*) FROM employees e WHERE {$where}");
    $stmt->execute($params);
    $stats['total'] = $stmt->fetchColumn();
    
    // Active Employees
    $stmt = $db->prepare("SELECT COUNT(*) FROM employees e WHERE {$where} AND employment_status = 'active'");
    $stmt->execute($params);
    $stats['active'] = $stmt->fetchColumn();
    
    // Inactive Employees
    $stmt = $db->prepare("SELECT COUNT(*) FROM employees e WHERE {$where} AND employment_status = 'inactive'");
    $stmt->execute($params);
    $stats['inactive'] = $stmt->fetchColumn();
    
    // On Leave
    $stmt = $db->prepare("SELECT COUNT(*) FROM employees e WHERE {$where} AND employment_status = 'on_leave'");
    $stmt->execute($params);
    $stats['on_leave'] = $stmt->fetchColumn();
    
    // Department-wise Distribution
    $stmt = $db->prepare("
        SELECT 
            d.department_name,
            COUNT(*) as employees,
            SUM(CASE WHEN e.employment_status = 'active' THEN 1 ELSE 0 END) as active
        FROM employees e
        LEFT JOIN departments d ON e.department_id = d.department_id
        WHERE e.site_id = ?
        GROUP BY d.department_id, d.department_name
        ORDER BY employees DESC
    ");
    $stmt->execute([$siteId]);
    $deptDistribution = $stmt->fetchAll();
    
    // Designation-wise Distribution
    $stmt = $db->prepare("
        SELECT 
            des.designation_name,
            COUNT(*) as employees,
            SUM(CASE WHEN e.employment_status = 'active' THEN 1 ELSE 0 END) as active
        FROM employees e
        LEFT JOIN designations des ON e.designation_id = des.designation_id
        WHERE e.site_id = ?
        GROUP BY des.designation_id, des.designation_name
        ORDER BY employees DESC
    ");
    $stmt->execute([$siteId]);
    $desgDistribution = $stmt->fetchAll();
    
    // Gender Distribution
    $stmt = $db->prepare("
        SELECT 
            gender,
            COUNT(*) as count
        FROM employees e
        WHERE {$where}
        GROUP BY gender
    ");
    $stmt->execute($params);
    $genderDistribution = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);
    
    // Age Distribution
    $stmt = $db->prepare("
        SELECT 
            CASE 
                WHEN TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) < 25 THEN 'Under 25'
                WHEN TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) BETWEEN 25 AND 30 THEN '25-30'
                WHEN TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) BETWEEN 31 AND 35 THEN '31-35'
                WHEN TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) BETWEEN 36 AND 40 THEN '36-40'
                WHEN TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) BETWEEN 41 AND 45 THEN '41-45'
                WHEN TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) BETWEEN 46 AND 50 THEN '46-50'
                ELSE 'Above 50'
            END as age_group,
            COUNT(*) as count
        FROM employees e
        WHERE {$where} AND date_of_birth IS NOT NULL
        GROUP BY age_group
        ORDER BY MIN(TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()))
    ");
    $stmt->execute($params);
    $ageDistribution = $stmt->fetchAll();
    
    // Joining Trend (Last 12 months)
    $stmt = $db->prepare("
        SELECT 
            DATE_FORMAT(joining_date, '%Y-%m') as month,
            COUNT(*) as joined
        FROM employees e
        WHERE {$where} AND joining_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
        GROUP BY DATE_FORMAT(joining_date, '%Y-%m')
        ORDER BY month
    ");
    $stmt->execute($params);
    $joiningTrend = $stmt->fetchAll();
    
} catch (Exception $e) {
    error_log("Employee report error: " . $e->getMessage());
    $stats = ['total' => 0, 'active' => 0, 'inactive' => 0, 'on_leave' => 0];
    $deptDistribution = [];
    $desgDistribution = [];
    $genderDistribution = [];
    $ageDistribution = [];
    $joiningTrend = [];
    $departments = [];
    $designations = [];
}

include __DIR__ . '/../../includes/header.php';
?>

<div class="card">
    <div class="card-header">
        <h2 class="card-title">Employee Reports & Statistics</h2>
        <div style="display: flex; gap: 10px;">
            <a href="<?php echo BASE_URL; ?>/modules/reports/index.php" class="btn btn-secondary">Back to Reports</a>
            <button onclick="exportReport('excel')" class="btn btn-primary">Export Excel</button>
            <button onclick="exportReport('pdf')" class="btn btn-primary">Export PDF</button>
        </div>
    </div>
    
    <!-- Filters -->
    <div style="padding: 20px; background: #f8fafc; border-radius: 8px; margin-bottom: 20px;">
        <form method="GET" style="display: grid; grid-template-columns: repeat(2, 1fr); gap: 15px; align-items: end;">
            <div class="form-group">
                <label class="form-label">Department</label>
                <select name="department_id" class="form-control" onchange="this.form.submit()">
                    <option value="0">All Departments</option>
                    <?php foreach ($departments as $dept): ?>
                    <option value="<?php echo $dept['department_id']; ?>" <?php echo $departmentId == $dept['department_id'] ? 'selected' : ''; ?>>
                        <?php echo htmlspecialchars($dept['department_name']); ?>
                    </option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="form-group">
                <label class="form-label">Designation</label>
                <select name="designation_id" class="form-control" onchange="this.form.submit()">
                    <option value="0">All Designations</option>
                    <?php foreach ($designations as $desg): ?>
                    <option value="<?php echo $desg['designation_id']; ?>" <?php echo $designationId == $desg['designation_id'] ? 'selected' : ''; ?>>
                        <?php echo htmlspecialchars($desg['designation_name']); ?>
                    </option>
                    <?php endforeach; ?>
                </select>
            </div>
        </form>
    </div>
    
    <!-- Statistics Cards -->
    <div style="display: grid; grid-template-columns: repeat(4, 1fr); gap: 20px; margin-bottom: 30px;">
        <div class="card" style="padding: 20px; text-align: center; background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); color: white;">
            <div style="font-size: 32px; font-weight: 600;"><?php echo number_format($stats['total']); ?></div>
            <div style="margin-top: 5px; opacity: 0.9;">Total Employees</div>
        </div>
        <div class="card" style="padding: 20px; text-align: center; background: linear-gradient(135deg, #11998e 0%, #38ef7d 100%); color: white;">
            <div style="font-size: 32px; font-weight: 600;"><?php echo number_format($stats['active']); ?></div>
            <div style="margin-top: 5px; opacity: 0.9;">Active</div>
        </div>
        <div class="card" style="padding: 20px; text-align: center; background: linear-gradient(135deg, #ee0979 0%, #ff6a00 100%); color: white;">
            <div style="font-size: 32px; font-weight: 600;"><?php echo number_format($stats['inactive']); ?></div>
            <div style="margin-top: 5px; opacity: 0.9;">Inactive</div>
        </div>
        <div class="card" style="padding: 20px; text-align: center; background: linear-gradient(135deg, #f093fb 0%, #f5576c 100%); color: white;">
            <div style="font-size: 32px; font-weight: 600;"><?php echo number_format($stats['on_leave']); ?></div>
            <div style="margin-top: 5px; opacity: 0.9;">On Leave</div>
        </div>
    </div>
    
    <!-- Department Distribution -->
    <?php if (count($deptDistribution) > 0): ?>
    <div class="card" style="margin-bottom: 30px;">
        <h3 style="margin-bottom: 20px; padding-bottom: 10px; border-bottom: 1px solid var(--border-color);">Department-wise Distribution</h3>
        <table class="data-table">
            <thead>
                <tr>
                    <th>Department</th>
                    <th>Total Employees</th>
                    <th>Active</th>
                    <th>Inactive</th>
                    <th>Percentage</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($deptDistribution as $dept): 
                    $percentage = $stats['total'] > 0 ? ($dept['employees'] / $stats['total']) * 100 : 0;
                ?>
                <tr>
                    <td><strong><?php echo htmlspecialchars($dept['department_name'] ?? 'N/A'); ?></strong></td>
                    <td><?php echo number_format($dept['employees']); ?></td>
                    <td style="color: var(--success-color);"><?php echo number_format($dept['active']); ?></td>
                    <td style="color: var(--error-color);"><?php echo number_format($dept['employees'] - $dept['active']); ?></td>
                    <td><?php echo number_format($percentage, 1); ?>%</td>
                </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    </div>
    <?php endif; ?>
    
    <!-- Designation Distribution -->
    <?php if (count($desgDistribution) > 0): ?>
    <div class="card" style="margin-bottom: 30px;">
        <h3 style="margin-bottom: 20px; padding-bottom: 10px; border-bottom: 1px solid var(--border-color);">Designation-wise Distribution</h3>
        <table class="data-table">
            <thead>
                <tr>
                    <th>Designation</th>
                    <th>Total Employees</th>
                    <th>Active</th>
                    <th>Inactive</th>
                    <th>Percentage</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($desgDistribution as $desg): 
                    $percentage = $stats['total'] > 0 ? ($desg['employees'] / $stats['total']) * 100 : 0;
                ?>
                <tr>
                    <td><strong><?php echo htmlspecialchars($desg['designation_name'] ?? 'N/A'); ?></strong></td>
                    <td><?php echo number_format($desg['employees']); ?></td>
                    <td style="color: var(--success-color);"><?php echo number_format($desg['active']); ?></td>
                    <td style="color: var(--error-color);"><?php echo number_format($desg['employees'] - $desg['active']); ?></td>
                    <td><?php echo number_format($percentage, 1); ?>%</td>
                </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    </div>
    <?php endif; ?>
    
    <!-- Demographics -->
    <div style="display: grid; grid-template-columns: repeat(2, 1fr); gap: 20px; margin-bottom: 30px;">
        <!-- Gender Distribution -->
        <div class="card">
            <h3 style="margin-bottom: 15px; padding-bottom: 10px; border-bottom: 1px solid var(--border-color);">Gender Distribution</h3>
            <div style="display: grid; grid-template-columns: repeat(2, 1fr); gap: 15px;">
                <?php 
                $genders = ['Male' => '#3b82f6', 'Female' => '#ec4899', 'Other' => '#8b5cf6'];
                foreach ($genders as $gender => $color): 
                    $count = $genderDistribution[strtolower($gender)] ?? 0;
                    $percentage = $stats['total'] > 0 ? ($count / $stats['total']) * 100 : 0;
                ?>
                <div style="text-align: center; padding: 15px; background: #f8fafc; border-radius: 8px;">
                    <div style="font-size: 24px; font-weight: 600; color: <?php echo $color; ?>;">
                        <?php echo number_format($count); ?>
                    </div>
                    <div style="margin-top: 5px; color: var(--text-secondary);"><?php echo $gender; ?></div>
                    <div style="margin-top: 5px; font-size: 12px; color: var(--text-secondary);">
                        <?php echo number_format($percentage, 1); ?>%
                    </div>
                </div>
                <?php endforeach; ?>
            </div>
        </div>
        
        <!-- Age Distribution -->
        <div class="card">
            <h3 style="margin-bottom: 15px; padding-bottom: 10px; border-bottom: 1px solid var(--border-color);">Age Distribution</h3>
            <div style="display: flex; flex-direction: column; gap: 10px;">
                <?php foreach ($ageDistribution as $age): 
                    $percentage = $stats['total'] > 0 ? ($age['count'] / $stats['total']) * 100 : 0;
                ?>
                <div>
                    <div style="display: flex; justify-content: space-between; margin-bottom: 5px;">
                        <span><?php echo htmlspecialchars($age['age_group']); ?></span>
                        <span style="font-weight: 600;"><?php echo number_format($age['count']); ?> (<?php echo number_format($percentage, 1); ?>%)</span>
                    </div>
                    <div style="height: 8px; background: #e5e7eb; border-radius: 4px; overflow: hidden;">
                        <div style="height: 100%; width: <?php echo $percentage; ?>%; background: linear-gradient(90deg, #667eea 0%, #764ba2 100%);"></div>
                    </div>
                </div>
                <?php endforeach; ?>
            </div>
        </div>
    </div>
</div>

<script>
function exportReport(format) {
    const params = new URLSearchParams(window.location.search);
    params.append('export', format);
    window.location.href = '<?php echo BASE_URL; ?>/modules/reports/export_employee.php?' + params.toString();
}
</script>

<?php include __DIR__ . '/../../includes/footer.php'; ?>

