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

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

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

$siteId = getCurrentSiteId();
$dateFrom = $_GET['date_from'] ?? date('Y-01-01');
$dateTo = $_GET['date_to'] ?? date('Y-12-31');
$leaveTypeId = isset($_GET['leave_type_id']) ? (int)$_GET['leave_type_id'] : 0;

try {
    $db = getDB();
    
    // Get leave types for filter
    $stmt = $db->prepare("SELECT leave_type_id, leave_type_name FROM leave_types WHERE site_id = ? ORDER BY leave_type_name");
    $stmt->execute([$siteId]);
    $leaveTypes = $stmt->fetchAll();
    
    // Overall Statistics
    $stats = [];
    
    // Total Applications
    $where = "la.site_id = ? AND la.applied_at BETWEEN ? AND ?";
    $params = [$siteId, $dateFrom, $dateTo];
    
    if ($leaveTypeId > 0) {
        $where .= " AND la.leave_type_id = ?";
        $params[] = $leaveTypeId;
    }
    
    $stmt = $db->prepare("SELECT COUNT(*) FROM leave_applications la WHERE {$where}");
    $stmt->execute($params);
    $stats['total_applications'] = $stmt->fetchColumn();
    
    // Status-wise counts
    $statuses = ['pending', 'approved', 'rejected', 'cancelled'];
    foreach ($statuses as $status) {
        $stmt = $db->prepare("SELECT COUNT(*) FROM leave_applications la WHERE {$where} AND la.application_status = ?");
        $statusParams = array_merge($params, [$status]);
        $stmt->execute($statusParams);
        $stats[$status] = $stmt->fetchColumn();
    }
    
    // Total Days
    $stmt = $db->prepare("SELECT COALESCE(SUM(total_days), 0) FROM leave_applications la WHERE {$where} AND la.application_status = 'approved'");
    $stmt->execute($params);
    $stats['total_days'] = $stmt->fetchColumn();
    
    // Leave Type Breakdown
    $stmt = $db->prepare("
        SELECT 
            lt.leave_type_name,
            COUNT(*) as applications,
            SUM(CASE WHEN la.application_status = 'approved' THEN la.total_days ELSE 0 END) as approved_days,
            SUM(CASE WHEN la.application_status = 'pending' THEN 1 ELSE 0 END) as pending
        FROM leave_applications la
        LEFT JOIN leave_types lt ON la.leave_type_id = lt.leave_type_id
        WHERE {$where}
        GROUP BY lt.leave_type_id, lt.leave_type_name
        ORDER BY applications DESC
    ");
    $stmt->execute($params);
    $typeBreakdown = $stmt->fetchAll();
    
    // Monthly Trend
    $stmt = $db->prepare("
        SELECT 
            DATE_FORMAT(la.applied_at, '%Y-%m') as month,
            COUNT(*) as applications,
            SUM(CASE WHEN la.application_status = 'approved' THEN la.total_days ELSE 0 END) as approved_days
        FROM leave_applications la
        WHERE {$where}
        GROUP BY DATE_FORMAT(la.applied_at, '%Y-%m')
        ORDER BY month
    ");
    $stmt->execute($params);
    $monthlyTrend = $stmt->fetchAll();
    
    // Department-wise Leave Usage
    $stmt = $db->prepare("
        SELECT 
            d.department_name,
            COUNT(DISTINCT la.employee_id) as employees,
            COUNT(*) as applications,
            SUM(CASE WHEN la.application_status = 'approved' THEN la.total_days ELSE 0 END) as approved_days
        FROM leave_applications la
        INNER JOIN employees e ON la.employee_id = e.employee_id
        LEFT JOIN departments d ON e.department_id = d.department_id
        WHERE {$where}
        GROUP BY d.department_id, d.department_name
        ORDER BY approved_days DESC
    ");
    $stmt->execute($params);
    $deptUsage = $stmt->fetchAll();
    
} catch (Exception $e) {
    error_log("Leave report error: " . $e->getMessage());
    $stats = ['total_applications' => 0, 'pending' => 0, 'approved' => 0, 'rejected' => 0, 'cancelled' => 0, 'total_days' => 0];
    $typeBreakdown = [];
    $monthlyTrend = [];
    $deptUsage = [];
    $leaveTypes = [];
}

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

<div class="card">
    <div class="card-header">
        <h2 class="card-title">Leave Reports & Analytics</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(3, 1fr); gap: 15px; align-items: end;">
            <div class="form-group">
                <label class="form-label">From Date</label>
                <input type="date" name="date_from" class="form-control" value="<?php echo htmlspecialchars($dateFrom); ?>" onchange="this.form.submit()">
            </div>
            <div class="form-group">
                <label class="form-label">To Date</label>
                <input type="date" name="date_to" class="form-control" value="<?php echo htmlspecialchars($dateTo); ?>" onchange="this.form.submit()">
            </div>
            <div class="form-group">
                <label class="form-label">Leave Type</label>
                <select name="leave_type_id" class="form-control" onchange="this.form.submit()">
                    <option value="0">All Types</option>
                    <?php foreach ($leaveTypes as $type): ?>
                    <option value="<?php echo $type['leave_type_id']; ?>" <?php echo $leaveTypeId == $type['leave_type_id'] ? 'selected' : ''; ?>>
                        <?php echo htmlspecialchars($type['leave_type_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_applications']); ?></div>
            <div style="margin-top: 5px; opacity: 0.9;">Total Applications</div>
        </div>
        <div class="card" style="padding: 20px; text-align: center; background: linear-gradient(135deg, #fef3c7 0%, #f59e0b 100%); color: white;">
            <div style="font-size: 32px; font-weight: 600;"><?php echo number_format($stats['pending']); ?></div>
            <div style="margin-top: 5px; opacity: 0.9;">Pending</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['approved']); ?></div>
            <div style="margin-top: 5px; opacity: 0.9;">Approved</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['total_days']); ?></div>
            <div style="margin-top: 5px; opacity: 0.9;">Total Days</div>
        </div>
    </div>
    
    <!-- Approval Rate -->
    <div class="card" style="margin-bottom: 30px; padding: 20px;">
        <h3 style="margin-bottom: 15px;">Approval Statistics</h3>
        <?php 
        $approvalRate = $stats['total_applications'] > 0 ? ($stats['approved'] / $stats['total_applications']) * 100 : 0;
        $rejectionRate = $stats['total_applications'] > 0 ? ($stats['rejected'] / $stats['total_applications']) * 100 : 0;
        ?>
        <div style="display: grid; grid-template-columns: repeat(3, 1fr); gap: 20px;">
            <div>
                <div style="font-size: 28px; font-weight: 600; color: var(--success-color);">
                    <?php echo number_format($approvalRate, 1); ?>%
                </div>
                <div style="margin-top: 5px; color: var(--text-secondary);">Approval Rate</div>
            </div>
            <div>
                <div style="font-size: 28px; font-weight: 600; color: var(--error-color);">
                    <?php echo number_format($rejectionRate, 1); ?>%
                </div>
                <div style="margin-top: 5px; color: var(--text-secondary);">Rejection Rate</div>
            </div>
            <div>
                <div style="font-size: 28px; font-weight: 600; color: var(--warning-color);">
                    <?php echo number_format($stats['pending']); ?>
                </div>
                <div style="margin-top: 5px; color: var(--text-secondary);">Pending Applications</div>
            </div>
        </div>
    </div>
    
    <!-- Leave Type Breakdown -->
    <?php if (count($typeBreakdown) > 0): ?>
    <div class="card" style="margin-bottom: 30px;">
        <h3 style="margin-bottom: 20px; padding-bottom: 10px; border-bottom: 1px solid var(--border-color);">Leave Type Breakdown</h3>
        <table class="data-table">
            <thead>
                <tr>
                    <th>Leave Type</th>
                    <th>Applications</th>
                    <th>Pending</th>
                    <th>Approved Days</th>
                    <th>Avg Days/Application</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($typeBreakdown as $type): 
                    $avgDays = $type['applications'] > 0 ? $type['approved_days'] / $type['applications'] : 0;
                ?>
                <tr>
                    <td><strong><?php echo htmlspecialchars($type['leave_type_name'] ?? 'N/A'); ?></strong></td>
                    <td><?php echo number_format($type['applications']); ?></td>
                    <td style="color: var(--warning-color);"><?php echo number_format($type['pending']); ?></td>
                    <td style="color: var(--success-color);"><?php echo number_format($type['approved_days'], 1); ?></td>
                    <td><?php echo number_format($avgDays, 1); ?></td>
                </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    </div>
    <?php endif; ?>
    
    <!-- Department-wise Usage -->
    <?php if (count($deptUsage) > 0): ?>
    <div class="card">
        <h3 style="margin-bottom: 20px; padding-bottom: 10px; border-bottom: 1px solid var(--border-color);">Department-wise Leave Usage</h3>
        <table class="data-table">
            <thead>
                <tr>
                    <th>Department</th>
                    <th>Employees</th>
                    <th>Applications</th>
                    <th>Approved Days</th>
                    <th>Avg Days/Employee</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($deptUsage as $dept): 
                    $avgDays = $dept['employees'] > 0 ? $dept['approved_days'] / $dept['employees'] : 0;
                ?>
                <tr>
                    <td><strong><?php echo htmlspecialchars($dept['department_name'] ?? 'N/A'); ?></strong></td>
                    <td><?php echo number_format($dept['employees']); ?></td>
                    <td><?php echo number_format($dept['applications']); ?></td>
                    <td style="color: var(--success-color);"><?php echo number_format($dept['approved_days'], 1); ?></td>
                    <td><?php echo number_format($avgDays, 1); ?></td>
                </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    </div>
    <?php endif; ?>
</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_leave.php?' + params.toString();
}
</script>

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

