<?php
require_once '../config/config.php';
requireRole('admin');

$pageTitle = 'Sex Disaggregated Data - Admin - PSAU GAD';
$db = getDB();

// Get employee statistics by gender and category
$stmt = $db->query("
    SELECT 
        ep.category,
        ep.gender,
        COUNT(*) as count
    FROM employee_profiles ep
    JOIN users u ON ep.user_id = u.user_id
    WHERE u.role = 'employee'
    GROUP BY ep.category, ep.gender
    ORDER BY ep.category, ep.gender
");
$employee_data = $stmt->fetchAll();

// Organize data by category
$categories_data = [];
$total_male = 0;
$total_female = 0;

foreach ($employee_data as $row) {
    $category = $row['category'];
    $gender = $row['gender'];
    $count = $row['count'];
    
    if (!isset($categories_data[$category])) {
        $categories_data[$category] = ['Male' => 0, 'Female' => 0];
    }
    
    $categories_data[$category][$gender] = $count;
    
    if ($gender === 'Male') {
        $total_male += $count;
    } else {
        $total_female += $count;
    }
}

$total_employees = $total_male + $total_female;

// Get activity participation by gender
$year = $_GET['year'] ?? date('Y');
$stmt = $db->prepare("
    SELECT 
        a.category as activity_category,
        ep.gender,
        COUNT(DISTINCT aa.user_id) as participants
    FROM activity_attendance aa
    JOIN activities a ON aa.activity_id = a.activity_id
    JOIN employee_profiles ep ON aa.user_id = ep.user_id
    WHERE YEAR(a.activity_date) = ?
    GROUP BY a.category, ep.gender
    ORDER BY a.category, ep.gender
");
$stmt->execute([$year]);
$participation_data = $stmt->fetchAll();

// Organize participation data
$participation_by_category = [];
foreach ($participation_data as $row) {
    $category = $row['activity_category'];
    $gender = $row['gender'];
    $count = $row['participants'];
    
    if (!isset($participation_by_category[$category])) {
        $participation_by_category[$category] = ['Male' => 0, 'Female' => 0];
    }
    
    $participation_by_category[$category][$gender] = $count;
}

// Export functionality
if (isset($_GET['export']) && $_GET['export'] === 'excel') {
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="SDD_Report_' . date('Y-m-d') . '.csv"');
    
    $output = fopen('php://output', 'w');
    
    fputcsv($output, ['PAMPANGA STATE AGRICULTURAL UNIVERSITY']);
    fputcsv($output, ['SEX DISAGGREGATED DATA (SDD) REPORT']);
    fputcsv($output, ['Generated: ' . date('F d, Y')]);
    fputcsv($output, []);
    
    // Employee Distribution
    fputcsv($output, ['EMPLOYEE DISTRIBUTION BY CATEGORY AND GENDER']);
    fputcsv($output, ['Category', 'Male', 'Female', 'Total', '% Male', '% Female']);
    foreach ($categories_data as $category => $data) {
        $cat_total = $data['Male'] + $data['Female'];
        $male_pct = $cat_total > 0 ? round(($data['Male'] / $cat_total) * 100, 1) : 0;
        $female_pct = $cat_total > 0 ? round(($data['Female'] / $cat_total) * 100, 1) : 0;
        fputcsv($output, [
            $category,
            $data['Male'],
            $data['Female'],
            $cat_total,
            $male_pct . '%',
            $female_pct . '%'
        ]);
    }
    fputcsv($output, ['TOTAL', $total_male, $total_female, $total_employees, '', '']);
    fputcsv($output, []);
    
    // Activity Participation
    fputcsv($output, ['ACTIVITY PARTICIPATION BY CATEGORY AND GENDER (Year: ' . $year . ')']);
    fputcsv($output, ['Activity Category', 'Male', 'Female', 'Total']);
    foreach ($participation_by_category as $category => $data) {
        $total = $data['Male'] + $data['Female'];
        fputcsv($output, [
            $category,
            $data['Male'],
            $data['Female'],
            $total
        ]);
    }
    
    fclose($output);
    exit;
}

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

<div class="min-h-screen bg-gradient-to-br from-purple-50 via-white to-pink-50 py-8">
    <div class="max-w-7xl mx-auto px-4 sm:px-6 lg:px-8">
        
        <!-- Header -->
        <div class="mb-8">
            <div class="flex items-center justify-between">
                <div>
                    <h1 class="text-3xl font-bold text-gray-900">
                        <i class="fas fa-venus-mars text-admin mr-3"></i>
                        Sex Disaggregated Data (SDD)
                    </h1>
                    <p class="text-gray-600 mt-2">Gender-based statistical analysis</p>
                </div>
                <div class="flex gap-3">
                    <a href="?export=excel" 
                       class="bg-green-600 hover:bg-green-700 text-white px-6 py-3 rounded-lg font-semibold transition btn-animate flex items-center gap-2">
                        <i class="fas fa-file-excel"></i>
                        Export to Excel
                    </a>
                    <button onclick="window.print()" 
                            class="bg-purple-600 hover:bg-purple-700 text-white px-6 py-3 rounded-lg font-semibold transition btn-animate flex items-center gap-2">
                        <i class="fas fa-print"></i>
                        Print
                    </button>
                    <a href="reports.php" 
                       class="bg-gray-600 hover:bg-gray-700 text-white px-6 py-3 rounded-lg font-semibold transition btn-animate">
                        <i class="fas fa-arrow-left mr-2"></i>
                        Back
                    </a>
                </div>
            </div>
        </div>

        <!-- Report Header -->
        <div class="bg-white rounded-xl shadow-lg p-8 mb-8">
            <div class="text-center mb-6">
                <h2 class="text-2xl font-bold text-gray-900">PAMPANGA STATE AGRICULTURAL UNIVERSITY</h2>
                <h3 class="text-xl font-bold text-admin mt-2">SEX DISAGGREGATED DATA (SDD) REPORT</h3>
                <p class="text-lg text-gray-700 mt-2">Generated: <?php echo date('F d, Y'); ?></p>
            </div>

            <!-- Overall Summary -->
            <div class="grid grid-cols-1 md:grid-cols-3 gap-4 mb-8">
                <div class="bg-blue-50 rounded-lg p-6 text-center">
                    <div class="text-4xl font-bold text-blue-600"><?php echo $total_employees; ?></div>
                    <div class="text-sm text-gray-600 mt-2">Total Employees</div>
                </div>
                <div class="bg-indigo-50 rounded-lg p-6 text-center">
                    <div class="text-4xl font-bold text-indigo-600"><?php echo $total_male; ?></div>
                    <div class="text-sm text-gray-600 mt-2">Male (<?php echo $total_employees > 0 ? round(($total_male / $total_employees) * 100, 1) : 0; ?>%)</div>
                </div>
                <div class="bg-pink-50 rounded-lg p-6 text-center">
                    <div class="text-4xl font-bold text-pink-600"><?php echo $total_female; ?></div>
                    <div class="text-sm text-gray-600 mt-2">Female (<?php echo $total_employees > 0 ? round(($total_female / $total_employees) * 100, 1) : 0; ?>%)</div>
                </div>
            </div>

            <!-- Gender Ratio Visualization -->
            <div class="mb-8">
                <h4 class="font-semibold text-gray-700 mb-4">Overall Gender Distribution</h4>
                <div class="flex h-8 rounded-lg overflow-hidden">
                    <div class="bg-indigo-500 flex items-center justify-center text-white text-sm font-semibold" 
                         style="width: <?php echo $total_employees > 0 ? ($total_male / $total_employees) * 100 : 0; ?>%">
                        <?php if ($total_male > 0): ?>Male: <?php echo $total_male; ?><?php endif; ?>
                    </div>
                    <div class="bg-pink-500 flex items-center justify-center text-white text-sm font-semibold" 
                         style="width: <?php echo $total_employees > 0 ? ($total_female / $total_employees) * 100 : 0; ?>%">
                        <?php if ($total_female > 0): ?>Female: <?php echo $total_female; ?><?php endif; ?>
                    </div>
                </div>
            </div>
        </div>

        <!-- Employee Distribution by Category -->
        <div class="bg-white rounded-xl shadow-lg p-6 mb-8">
            <h2 class="text-xl font-bold text-gray-900 mb-6">
                <i class="fas fa-users text-admin mr-2"></i>
                Employee Distribution by Category and Gender
            </h2>
            <div class="overflow-x-auto">
                <table class="min-w-full divide-y divide-gray-200">
                    <thead class="bg-gray-50">
                        <tr>
                            <th class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">Category</th>
                            <th class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">Male</th>
                            <th class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">Female</th>
                            <th class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">Total</th>
                            <th class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">Distribution</th>
                        </tr>
                    </thead>
                    <tbody class="bg-white divide-y divide-gray-200">
                        <?php foreach ($categories_data as $category => $data): 
                            $cat_total = $data['Male'] + $data['Female'];
                            $male_pct = $cat_total > 0 ? round(($data['Male'] / $cat_total) * 100, 1) : 0;
                            $female_pct = $cat_total > 0 ? round(($data['Female'] / $cat_total) * 100, 1) : 0;
                        ?>
                            <tr class="hover:bg-gray-50">
                                <td class="px-6 py-4 font-semibold text-gray-900"><?php echo htmlspecialchars($category); ?></td>
                                <td class="px-6 py-4">
                                    <div class="text-sm font-semibold text-indigo-600"><?php echo $data['Male']; ?></div>
                                    <div class="text-xs text-gray-500"><?php echo $male_pct; ?>%</div>
                                </td>
                                <td class="px-6 py-4">
                                    <div class="text-sm font-semibold text-pink-600"><?php echo $data['Female']; ?></div>
                                    <div class="text-xs text-gray-500"><?php echo $female_pct; ?>%</div>
                                </td>
                                <td class="px-6 py-4 font-semibold text-gray-900"><?php echo $cat_total; ?></td>
                                <td class="px-6 py-4">
                                    <div class="flex h-6 rounded overflow-hidden">
                                        <div class="bg-indigo-400" style="width: <?php echo $male_pct; ?>%"></div>
                                        <div class="bg-pink-400" style="width: <?php echo $female_pct; ?>%"></div>
                                    </div>
                                </td>
                            </tr>
                        <?php endforeach; ?>
                        <tr class="bg-gray-50 font-bold">
                            <td class="px-6 py-4 text-gray-900">TOTAL</td>
                            <td class="px-6 py-4 text-indigo-600"><?php echo $total_male; ?></td>
                            <td class="px-6 py-4 text-pink-600"><?php echo $total_female; ?></td>
                            <td class="px-6 py-4 text-gray-900"><?php echo $total_employees; ?></td>
                            <td class="px-6 py-4"></td>
                        </tr>
                    </tbody>
                </table>
            </div>
        </div>

        <!-- Activity Participation by Gender -->
        <div class="bg-white rounded-xl shadow-lg p-6 mb-8">
            <div class="flex items-center justify-between mb-6">
                <h2 class="text-xl font-bold text-gray-900">
                    <i class="fas fa-chart-bar text-admin mr-2"></i>
                    Activity Participation by Gender (<?php echo $year; ?>)
                </h2>
                <form method="GET" class="flex gap-2">
                    <select name="year" class="px-4 py-2 border border-gray-300 rounded-lg focus:ring-2 focus:ring-admin focus:border-transparent">
                        <?php for ($y = date('Y'); $y >= 2020; $y--): ?>
                            <option value="<?php echo $y; ?>" <?php echo $y == $year ? 'selected' : ''; ?>><?php echo $y; ?></option>
                        <?php endfor; ?>
                    </select>
                    <button type="submit" class="bg-admin hover:bg-admin-dark text-white px-4 py-2 rounded-lg font-semibold transition">
                        <i class="fas fa-filter"></i>
                    </button>
                </form>
            </div>

            <?php if (empty($participation_by_category)): ?>
                <div class="text-center py-8 text-gray-500">
                    <i class="fas fa-inbox text-4xl mb-3"></i>
                    <p>No participation data for <?php echo $year; ?></p>
                </div>
            <?php else: ?>
                <div class="overflow-x-auto">
                    <table class="min-w-full divide-y divide-gray-200">
                        <thead class="bg-gray-50">
                            <tr>
                                <th class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">Activity Category</th>
                                <th class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">Male</th>
                                <th class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">Female</th>
                                <th class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">Total</th>
                                <th class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">Distribution</th>
                            </tr>
                        </thead>
                        <tbody class="bg-white divide-y divide-gray-200">
                            <?php foreach ($participation_by_category as $category => $data): 
                                $total = $data['Male'] + $data['Female'];
                                $male_pct = $total > 0 ? round(($data['Male'] / $total) * 100, 1) : 0;
                                $female_pct = $total > 0 ? round(($data['Female'] / $total) * 100, 1) : 0;
                            ?>
                                <tr class="hover:bg-gray-50">
                                    <td class="px-6 py-4 font-semibold text-gray-900"><?php echo htmlspecialchars($category); ?></td>
                                    <td class="px-6 py-4">
                                        <div class="text-sm font-semibold text-indigo-600"><?php echo $data['Male']; ?></div>
                                        <div class="text-xs text-gray-500"><?php echo $male_pct; ?>%</div>
                                    </td>
                                    <td class="px-6 py-4">
                                        <div class="text-sm font-semibold text-pink-600"><?php echo $data['Female']; ?></div>
                                        <div class="text-xs text-gray-500"><?php echo $female_pct; ?>%</div>
                                    </td>
                                    <td class="px-6 py-4 font-semibold text-gray-900"><?php echo $total; ?></td>
                                    <td class="px-6 py-4">
                                        <div class="flex h-6 rounded overflow-hidden">
                                            <div class="bg-indigo-400" style="width: <?php echo $male_pct; ?>%"></div>
                                            <div class="bg-pink-400" style="width: <?php echo $female_pct; ?>%"></div>
                                        </div>
                                    </td>
                                </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            <?php endif; ?>
        </div>

        <!-- Key Insights -->
        <div class="bg-white rounded-xl shadow-lg p-6">
            <h2 class="text-xl font-bold text-gray-900 mb-6">
                <i class="fas fa-lightbulb text-admin mr-2"></i>
                Key Insights
            </h2>
            <div class="grid grid-cols-1 md:grid-cols-2 gap-6">
                <div class="border-l-4 border-admin pl-4">
                    <h4 class="font-semibold text-gray-900 mb-2">Gender Balance</h4>
                    <p class="text-sm text-gray-600">
                        The overall gender distribution is 
                        <span class="font-semibold text-indigo-600"><?php echo $total_employees > 0 ? round(($total_male / $total_employees) * 100, 1) : 0; ?>% Male</span> and 
                        <span class="font-semibold text-pink-600"><?php echo $total_employees > 0 ? round(($total_female / $total_employees) * 100, 1) : 0; ?>% Female</span>.
                    </p>
                </div>
                <div class="border-l-4 border-admin pl-4">
                    <h4 class="font-semibold text-gray-900 mb-2">Category Distribution</h4>
                    <p class="text-sm text-gray-600">
                        Employee distribution varies across categories. Review the table above for detailed breakdown by employee category.
                    </p>
                </div>
                <div class="border-l-4 border-admin pl-4">
                    <h4 class="font-semibold text-gray-900 mb-2">GAD Participation</h4>
                    <p class="text-sm text-gray-600">
                        Activity participation data shows gender representation in GAD programs for <?php echo $year; ?>.
                    </p>
                </div>
                <div class="border-l-4 border-admin pl-4">
                    <h4 class="font-semibold text-gray-900 mb-2">Data Usage</h4>
                    <p class="text-sm text-gray-600">
                        This SDD report supports gender-responsive planning and monitoring of GAD initiatives.
                    </p>
                </div>
            </div>
        </div>

    </div>
</div>

<style>
@media print {
    .print\:hidden {
        display: none !important;
    }
    body {
        background: white !important;
    }
}
</style>

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

