<?php
/**
 * Membership Analytics - Comprehensive membership statistics and insights
 */

require_once __DIR__ . '/../../includes/auth.php';
require_once __DIR__ . '/../../includes/fellowships.php';
requireAuth();

// Only admin and system_admin can view analytics
if (!isAdmin() && !isSystemAdmin()) {
    header('Location: ' . BASE_PATH . '/members');
    exit;
}

$pageTitle = 'Membership Analytics';
$currentPage = 'members';

// Overall Statistics
$totalMembers = (int) $pdo->query("SELECT COUNT(*) FROM members WHERE (status != 'deceased' OR status IS NULL) AND (date_of_death IS NULL)")->fetchColumn();
$departedMembers = (int) $pdo->query("SELECT COUNT(*) FROM members WHERE status = 'deceased' OR date_of_death IS NOT NULL")->fetchColumn();
$totalHouseholds = (int) $pdo->query('SELECT COUNT(*) FROM households')->fetchColumn();
$totalMinistries = (int) $pdo->query('SELECT COUNT(*) FROM ministries')->fetchColumn();

// Membership by Status
$byStatus = $pdo->query("
    SELECT status, COUNT(*) as count 
    FROM members 
    WHERE (status != 'deceased' OR status IS NULL) AND (date_of_death IS NULL)
    GROUP BY status 
    ORDER BY count DESC
")->fetchAll();

// Membership by Gender
$byGender = $pdo->query("
    SELECT gender, COUNT(*) as count 
    FROM members 
    WHERE (status != 'deceased' OR status IS NULL) AND (date_of_death IS NULL) AND gender IS NOT NULL
    GROUP BY gender 
    ORDER BY count DESC
")->fetchAll();

// Membership by Fellowship
$allMembers = $pdo->query("
    SELECT date_of_birth, gender 
    FROM members 
    WHERE (status != 'deceased' OR status IS NULL) AND (date_of_death IS NULL)
")->fetchAll();

$byFellowship = [];
foreach ($allMembers as $m) {
    $fellowship = getMemberFellowship($m['date_of_birth'] ?? null, $m['gender'] ?? null) ?? 'Unknown';
    if (!isset($byFellowship[$fellowship])) {
        $byFellowship[$fellowship] = 0;
    }
    $byFellowship[$fellowship]++;
}
arsort($byFellowship);

// Service groups (ministries) with male/female and generational (fellowship) breakdown
$ministryRows = $pdo->query("
    SELECT mm.ministry_id, m.name AS ministry_name, mem.date_of_birth, mem.gender
    FROM member_ministries mm
    JOIN ministries m ON m.id = mm.ministry_id
    JOIN members mem ON mem.id = mm.member_id
    AND (mem.status != 'deceased' OR mem.status IS NULL)
    AND (mem.date_of_death IS NULL)
")->fetchAll();

$ministryDetails = [];
foreach ($ministryRows as $row) {
    $mid = (int) $row['ministry_id'];
    if (!isset($ministryDetails[$mid])) {
        $ministryDetails[$mid] = [
            'name' => $row['ministry_name'],
            'count' => 0,
            'male' => 0,
            'female' => 0,
            'by_fellowship' => [],
        ];
    }
    $ministryDetails[$mid]['count']++;
    $g = strtoupper(trim($row['gender'] ?? ''));
    if ($g === 'MALE' || $g === 'M') {
        $ministryDetails[$mid]['male']++;
    } elseif ($g === 'FEMALE' || $g === 'F') {
        $ministryDetails[$mid]['female']++;
    }
    $fellowship = getMemberFellowship($row['date_of_birth'] ?? null, $row['gender'] ?? null);
    if ($fellowship) {
        if (!isset($ministryDetails[$mid]['by_fellowship'][$fellowship])) {
            $ministryDetails[$mid]['by_fellowship'][$fellowship] = 0;
        }
        $ministryDetails[$mid]['by_fellowship'][$fellowship]++;
    }
}
uasort($ministryDetails, function ($a, $b) {
    return $b['count'] - $a['count'];
});

// Fellowship order for tables (only those that exist in population)
$fellowshipOrder = array_values(array_filter(getFellowshipOrder(), function ($f) use ($byFellowship) {
    return $f !== null && isset($byFellowship[$f]);
}));

// Generational groups (fellowships) as rows: count, male, female, and % in each service group
$activeMembersWithFellowship = $pdo->query("
    SELECT id, date_of_birth, gender
    FROM members
    WHERE (status != 'deceased' OR status IS NULL) AND (date_of_death IS NULL)
")->fetchAll();

$memberFellowship = []; // member_id => fellowship name
$fellowshipStats = []; // fellowship => [ count, male, female, by_ministry => [ mid => count ] ]

foreach ($activeMembersWithFellowship as $m) {
    $f = getMemberFellowship($m['date_of_birth'] ?? null, $m['gender'] ?? null);
    if ($f === null) {
        $f = 'Unknown';
    }
    $memberFellowship[(int) $m['id']] = $f;
    if (!isset($fellowshipStats[$f])) {
        $fellowshipStats[$f] = ['count' => 0, 'male' => 0, 'female' => 0, 'by_ministry' => []];
    }
    $fellowshipStats[$f]['count']++;
    $g = strtoupper(trim($m['gender'] ?? ''));
    if ($g === 'MALE' || $g === 'M') {
        $fellowshipStats[$f]['male']++;
    } elseif ($g === 'FEMALE' || $g === 'F') {
        $fellowshipStats[$f]['female']++;
    }
}

$memberMinistries = $pdo->query("
    SELECT mm.member_id, mm.ministry_id
    FROM member_ministries mm
    JOIN members mem ON mem.id = mm.member_id
    AND (mem.status != 'deceased' OR mem.status IS NULL)
    AND (mem.date_of_death IS NULL)
")->fetchAll();

foreach ($memberMinistries as $row) {
    $mid = (int) $row['ministry_id'];
    $f = $memberFellowship[(int) $row['member_id']] ?? 'Unknown';
    if (!isset($fellowshipStats[$f]['by_ministry'][$mid])) {
        $fellowshipStats[$f]['by_ministry'][$mid] = 0;
    }
    $fellowshipStats[$f]['by_ministry'][$mid]++;
}

// Order generational groups for table (standard order first, then any others e.g. Unknown)
$generationalRowsOrder = array_values(array_filter($fellowshipOrder, function ($f) use ($fellowshipStats) {
    return isset($fellowshipStats[$f]) && $fellowshipStats[$f]['count'] > 0;
}));
foreach (array_keys($fellowshipStats) as $f) {
    if ($fellowshipStats[$f]['count'] > 0 && !in_array($f, $generationalRowsOrder, true)) {
        $generationalRowsOrder[] = $f;
    }
}

// Age Groups
$ageGroups = [
    '0-12' => 0,
    '13-17' => 0,
    '18-29' => 0,
    '30-39' => 0,
    '40-49' => 0,
    '50-59' => 0,
    '60+' => 0
];

foreach ($allMembers as $m) {
    if (empty($m['date_of_birth'])) continue;
    $age = getMemberAge($m['date_of_birth']);
    if ($age === null) continue;
    
    if ($age <= 12) $ageGroups['0-12']++;
    elseif ($age <= 17) $ageGroups['13-17']++;
    elseif ($age <= 29) $ageGroups['18-29']++;
    elseif ($age <= 39) $ageGroups['30-39']++;
    elseif ($age <= 49) $ageGroups['40-49']++;
    elseif ($age <= 59) $ageGroups['50-59']++;
    else $ageGroups['60+']++;
}

// Baptism Statistics
$totalBaptized = (int) $pdo->query("
    SELECT COUNT(*) 
    FROM members 
    WHERE baptism_date IS NOT NULL 
    AND (status != 'deceased' OR status IS NULL) 
    AND (date_of_death IS NULL)
")->fetchColumn();

$baptismByYear = [];
$stmt = $pdo->query("
    SELECT YEAR(baptism_date) as year, COUNT(*) as count
    FROM members
    WHERE baptism_date IS NOT NULL 
    AND (status != 'deceased' OR status IS NULL) 
    AND (date_of_death IS NULL)
    GROUP BY YEAR(baptism_date)
    ORDER BY year DESC
    LIMIT 10
");
$baptismByYear = $stmt->fetchAll();

// Members without contact info
$noPhone = (int) $pdo->query("
    SELECT COUNT(*) 
    FROM members 
    WHERE (phone IS NULL OR phone = '') 
    AND (status != 'deceased' OR status IS NULL) 
    AND (date_of_death IS NULL)
")->fetchColumn();

$noEmail = (int) $pdo->query("
    SELECT COUNT(*) 
    FROM members 
    WHERE (email IS NULL OR email = '') 
    AND (status != 'deceased' OR status IS NULL) 
    AND (date_of_death IS NULL)
")->fetchColumn();

// Average age
$avgAge = null;
$stmt = $pdo->query("
    SELECT AVG(YEAR(CURDATE()) - YEAR(date_of_birth)) as avg_age
    FROM members
    WHERE date_of_birth IS NOT NULL
    AND (status != 'deceased' OR status IS NULL) 
    AND (date_of_death IS NULL)
");
$result = $stmt->fetch();
if ($result && $result['avg_age'] !== null) {
    $avgAge = round((float)$result['avg_age'], 1);
}

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

<div class="analytics-page">
    <header class="analytics-header">
        <div>
            <a href="<?= BASE_PATH ?>/members" class="analytics-back">← Members</a>
            <h1 class="analytics-title">Membership Analytics</h1>
            <p class="analytics-subtitle">Overview of membership status, demographics, and data quality</p>
        </div>
    </header>

    <!-- Key metrics – same visual language as dashboard -->
    <section class="analytics-stats" aria-label="Key metrics">
        <div class="analytics-stat-card analytics-stat-card-1">
            <span class="analytics-stat-icon"><i class="fas fa-users" aria-hidden="true"></i></span>
            <div class="analytics-stat-inner">
                <span class="analytics-stat-label">Total Active Members</span>
                <span class="analytics-stat-number"><?= number_format($totalMembers) ?></span>
            </div>
        </div>
        <div class="analytics-stat-card analytics-stat-card-2">
            <span class="analytics-stat-icon"><i class="fas fa-user-minus" aria-hidden="true"></i></span>
            <div class="analytics-stat-inner">
                <span class="analytics-stat-label">Departed Members</span>
                <span class="analytics-stat-number"><?= number_format($departedMembers) ?></span>
            </div>
        </div>
        <div class="analytics-stat-card analytics-stat-card-3">
            <span class="analytics-stat-icon"><i class="fas fa-home" aria-hidden="true"></i></span>
            <div class="analytics-stat-inner">
                <span class="analytics-stat-label">Households</span>
                <span class="analytics-stat-number"><?= number_format($totalHouseholds) ?></span>
            </div>
        </div>
        <div class="analytics-stat-card analytics-stat-card-4">
            <span class="analytics-stat-icon"><i class="fas fa-layer-group" aria-hidden="true"></i></span>
            <div class="analytics-stat-inner">
                <span class="analytics-stat-label">Service Groups</span>
                <span class="analytics-stat-number"><?= number_format($totalMinistries) ?></span>
            </div>
        </div>
        <?php if ($avgAge !== null): ?>
        <div class="analytics-stat-card analytics-stat-card-5">
            <span class="analytics-stat-icon"><i class="fas fa-birthday-cake" aria-hidden="true"></i></span>
            <div class="analytics-stat-inner">
                <span class="analytics-stat-label">Average Age</span>
                <span class="analytics-stat-number"><?= $avgAge ?> <span class="analytics-stat-unit">years</span></span>
            </div>
        </div>
        <?php endif; ?>
        <div class="analytics-stat-card analytics-stat-card-6">
            <span class="analytics-stat-icon"><i class="fas fa-water" aria-hidden="true"></i></span>
            <div class="analytics-stat-inner">
                <span class="analytics-stat-label">Baptized Members</span>
                <span class="analytics-stat-number"><?= number_format($totalBaptized) ?></span>
                <span class="analytics-stat-meta"><?= $totalMembers > 0 ? round(($totalBaptized / $totalMembers) * 100, 1) : 0 ?>% of active</span>
            </div>
        </div>
    </section>

    <!-- Charts and metrics -->
    <div class="analytics-charts">
        <div class="analytics-chart-card analytics-metrics-card">
            <h2 class="analytics-chart-title">Membership by Status</h2>
            <div class="analytics-metrics" role="group" aria-label="Membership by status">
                <?php
                $statusColors = ['youth' => 'blue', 'communicant' => 'green', 'child' => 'orange', 'member' => 'indigo', 'visitor' => 'slate', 'other' => 'gray'];
                foreach ($byStatus as $row):
                    $pct = $totalMembers > 0 ? round(($row['count'] / $totalMembers) * 100, 1) : 0;
                    $label = ucfirst(strtolower($row['status']));
                    $slug = strtolower(preg_replace('/\s+/', '-', $row['status']));
                    $color = $statusColors[$slug] ?? 'blue';
                ?>
                <div class="analytics-metric analytics-metric-<?= $color ?>">
                    <span class="analytics-metric-label"><?= htmlspecialchars($label) ?></span>
                    <span class="analytics-metric-value"><?= number_format($row['count']) ?></span>
                    <span class="analytics-metric-delta"><?= $pct ?>% of total</span>
                </div>
                <?php endforeach; ?>
            </div>
        </div>
        <div class="analytics-chart-card analytics-metrics-card">
            <h2 class="analytics-chart-title">Membership by Gender</h2>
            <div class="analytics-metrics" role="group" aria-label="Membership by gender">
                <?php foreach ($byGender as $row):
                    $pct = $totalMembers > 0 ? round(($row['count'] / $totalMembers) * 100, 1) : 0;
                    $label = ucfirst(strtolower($row['gender']));
                    $color = in_array(strtoupper($row['gender'] ?? ''), ['MALE', 'M'], true) ? 'male' : 'female';
                ?>
                <div class="analytics-metric analytics-metric-<?= $color ?>">
                    <span class="analytics-metric-label"><?= htmlspecialchars($label) ?></span>
                    <span class="analytics-metric-value"><?= number_format($row['count']) ?></span>
                    <span class="analytics-metric-delta"><?= $pct ?>% of total</span>
                </div>
                <?php endforeach; ?>
            </div>
        </div>
        <div class="analytics-chart-card">
            <h2 class="analytics-chart-title">Membership by Fellowship</h2>
            <div class="analytics-chart-wrap">
                <canvas id="fellowshipChart" role="img" aria-label="Bar chart of membership by fellowship"></canvas>
            </div>
        </div>
        <div class="analytics-chart-card">
            <h2 class="analytics-chart-title">Age Distribution</h2>
            <div class="analytics-chart-wrap">
                <canvas id="ageChart" role="img" aria-label="Bar chart of age distribution"></canvas>
            </div>
        </div>
    </div>

    <!-- Generational Groups -->
    <div class="analytics-card">
        <div class="analytics-card-head">
            <h2 class="analytics-card-title">Generational groups</h2>
            <a href="<?= BASE_PATH ?>/fellowships" class="analytics-card-action">View Gen. Grps →</a>
        </div>
        <div class="analytics-card-body">
            <?php
            if (empty($generationalRowsOrder)): ?>
            <p class="analytics-empty">No generational group data yet.</p>
            <?php else:
                $totalGenMembers = 0;
                $totalGenMale = 0;
                $totalGenFemale = 0;
                foreach ($fellowshipStats as $s) {
                    $totalGenMembers += $s['count'];
                    $totalGenMale += $s['male'];
                    $totalGenFemale += $s['female'];
                }
            ?>
            <div class="table-wrap analytics-table-wrap">
                <table class="analytics-table">
                    <thead>
                        <tr>
                            <th>Generational group</th>
                            <th>Members</th>
                            <th>Male</th>
                            <th>Female</th>
                            <th>% of church</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($generationalRowsOrder as $fname):
                            $s = $fellowshipStats[$fname];
                            $total = $s['count'];
                            $pctOfChurch = $totalMembers > 0 ? round(($total / $totalMembers) * 100, 1) : 0;
                        ?>
                        <tr>
                            <td><?= htmlspecialchars($fname) ?></td>
                            <td><?= number_format($total) ?></td>
                            <td><?= number_format($s['male']) ?></td>
                            <td><?= number_format($s['female']) ?></td>
                            <td><?= $pctOfChurch ?>%</td>
                        </tr>
                        <?php endforeach; ?>
                    </tbody>
                    <tfoot>
                        <tr class="analytics-table-totals">
                            <td>Total</td>
                            <td><?= number_format($totalGenMembers) ?></td>
                            <td><?= number_format($totalGenMale) ?></td>
                            <td><?= number_format($totalGenFemale) ?></td>
                            <td>100%</td>
                        </tr>
                    </tfoot>
                </table>
            </div>
            <?php endif; ?>
        </div>
    </div>

    <!-- Data quality -->
    <div class="analytics-card analytics-card-quality">
        <div class="analytics-card-head">
            <h2 class="analytics-card-title">Data quality</h2>
            <?php if ($noPhone > 0 || $noEmail > 0): ?>
            <a href="<?= BASE_PATH ?>/members" class="analytics-card-action">Update members →</a>
            <?php endif; ?>
        </div>
        <div class="analytics-card-body">
            <div class="analytics-quality-grid">
                <div class="analytics-quality-item <?= $noPhone > 0 ? 'has-issue' : 'ok' ?>">
                    <span class="analytics-quality-icon"><?= $noPhone > 0 ? '📞' : '✓' ?></span>
                    <div>
                        <span class="analytics-quality-label">Missing phone</span>
                        <span class="analytics-quality-value"><?= number_format($noPhone) ?></span>
                    </div>
                </div>
                <div class="analytics-quality-item <?= $noEmail > 0 ? 'has-issue' : 'ok' ?>">
                    <span class="analytics-quality-icon"><?= $noEmail > 0 ? '✉' : '✓' ?></span>
                    <div>
                        <span class="analytics-quality-label">Missing email</span>
                        <span class="analytics-quality-value"><?= number_format($noEmail) ?></span>
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>

<script src="https://cdn.jsdelivr.net/npm/chart.js@4.4.0/dist/chart.umd.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/chartjs-plugin-datalabels@2.2.0/dist/chartjs-plugin-datalabels.min.js"></script>
<script>
(function() {
    const chartDefaults = {
        responsive: true,
        maintainAspectRatio: true,
        layout: { padding: { top: 20, right: 12, bottom: 12, left: 12 } }
    };
    const noGrid = {
        grid: { display: false },
        border: { display: true, color: '#e2e8f0' },
        ticks: { color: '#718096', font: { size: 11 } }
    };
    const barDataLabels = {
        anchor: 'end',
        align: 'top',
        formatter: function(value) { return value; },
        color: '#4a5568',
        font: { size: 12, weight: '500' },
        offset: 4
    };

    // Fellowship Chart (bar, no grid)
    new Chart(document.getElementById('fellowshipChart'), {
        type: 'bar',
        data: {
            labels: <?= json_encode(array_keys($byFellowship)) ?>,
            datasets: [{
                label: 'Members',
                data: <?= json_encode(array_values($byFellowship)) ?>,
                backgroundColor: 'rgba(59, 130, 246, 0.85)',
                borderRadius: 6,
                borderSkipped: false
            }]
        },
        options: {
            ...chartDefaults,
            aspectRatio: 1.5,
            scales: {
                x: { ...noGrid },
                y: {
                    beginAtZero: true,
                    ...noGrid
                }
            },
            plugins: {
                legend: { display: false },
                datalabels: barDataLabels
            }
        }
    });

    // Age Chart (bar, no grid)
    new Chart(document.getElementById('ageChart'), {
        type: 'bar',
        data: {
            labels: <?= json_encode(array_keys($ageGroups)) ?>,
            datasets: [{
                label: 'Members',
                data: <?= json_encode(array_values($ageGroups)) ?>,
                backgroundColor: 'rgba(16, 185, 129, 0.85)',
                borderRadius: 6,
                borderSkipped: false
            }]
        },
        options: {
            ...chartDefaults,
            aspectRatio: 1.5,
            scales: {
                x: { ...noGrid },
                y: {
                    beginAtZero: true,
                    ...noGrid
                }
            },
            plugins: {
                legend: { display: false },
                datalabels: barDataLabels
            }
        }
    });
})();
</script>

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