<?php
include('inc/check_login.php');
require_once('inc/db.php');

// Gesamtanzahl abgeschlossener Treffen (nur zur Info)
$total_meetings = 0;
$result_total_meetings = mysqli_query($conn, "SELECT COUNT(*) AS total FROM meetings WHERE is_completed = 1");
if ($row = mysqli_fetch_assoc($result_total_meetings)) {
    $total_meetings = (int)$row['total'];
}

// Statistik: Farbhäufigkeit
$color_stats = [];
$sql_colors = "
    SELECT c.name, c.hex_code, COUNT(m.id) AS meeting_count
    FROM meetings m
    JOIN colors c ON m.color_id = c.id
    WHERE m.is_completed = 1
    GROUP BY c.name, c.hex_code
    ORDER BY meeting_count DESC
";
$result = mysqli_query($conn, $sql_colors);
while ($row = mysqli_fetch_assoc($result)) {
    $color_stats[] = $row;
}

// TEILNAHME: Sortiert nach Prozent, mit Prozentwert
$participation_stats = [];
$participation_percentages = [];
$participation_registered = [];
$participation_attended = [];

$sql_participation = "
    SELECT
        u.username,
        COUNT(*) AS registered_completed,
        SUM(CASE WHEN mt.attended = 1 THEN 1 ELSE 0 END) AS total_attendance,
        ROUND(
            (SUM(CASE WHEN mt.attended = 1 THEN 1 ELSE 0 END) / COUNT(*)) * 100,
            1
        ) AS percentage
    FROM meeting_teilnehmer mt
    JOIN meetings m ON mt.meeting_id = m.id AND m.is_completed = 1
    JOIN users u ON mt.user_id = u.id
    GROUP BY u.id, u.username
    ORDER BY percentage DESC, u.username ASC
";
$result = mysqli_query($conn, $sql_participation);
while ($row = mysqli_fetch_assoc($result)) {
    $registered = (int)$row['registered_completed'];
    $attended = (int)$row['total_attendance'];
    $percentage = (float)$row['percentage'];
    $participation_stats[] = [
        'username' => $row['username'],
        'total_attendance' => $attended,
        'registered_meetings' => $registered,
        'percentage' => $percentage
    ];
    $participation_percentages[] = $percentage;
    $participation_registered[] = $registered;
    $participation_attended[] = $attended;
}

// Globale Durchschnitte (unverändert)
$avg_attendance = 0;
$sql_avg_attendance = "
    SELECT AVG(attended_count) AS avg_attended
    FROM (
        SELECT COUNT(mt.user_id) AS attended_count
        FROM meeting_teilnehmer mt
        JOIN meetings m ON mt.meeting_id = m.id
        WHERE mt.attended = 1 AND m.is_completed = 1
        GROUP BY mt.meeting_id
    ) AS subquery";
$result_avg = mysqli_query($conn, $sql_avg_attendance);
if ($row = mysqli_fetch_assoc($result_avg)) {
    $avg_attendance = round($row['avg_attended'], 2);
}

$avg_wore_color = 0;
$sql_avg_wore_color = "
    SELECT AVG(wore_color_count) AS avg_wore_color
    FROM (
        SELECT COUNT(mt.user_id) AS wore_color_count
        FROM meeting_teilnehmer mt
        JOIN meetings m ON mt.meeting_id = m.id
        WHERE mt.wore_color = 1 AND m.is_completed = 1
        GROUP BY mt.meeting_id
    ) AS subquery";
$result_avg_wore = mysqli_query($conn, $sql_avg_wore_color);
if ($row = mysqli_fetch_assoc($result_avg_wore)) {
    $avg_wore_color = round($row['avg_wore_color'], 2);
}

// FARBE GETRAGEN: Sortiert nach Prozent, mit Prozentwert
$wore_color_stats = [];
$wore_color_percentages = [];
$wore_color_attended = [];
$wore_color_count = [];

$sql_wore_color = "
    SELECT
        u.username,
        SUM(CASE WHEN mt.attended = 1 THEN 1 ELSE 0 END) AS total_attendance,
        SUM(CASE WHEN mt.wore_color = 1 THEN 1 ELSE 0 END) AS wore_color_count,
        CASE
            WHEN SUM(CASE WHEN mt.attended = 1 THEN 1 ELSE 0 END) > 0 THEN
                ROUND(
                    (SUM(CASE WHEN mt.wore_color = 1 THEN 1 ELSE 0 END) /
                     SUM(CASE WHEN mt.attended = 1 THEN 1 ELSE 0 END)) * 100,
                    1
                )
            ELSE 0.0
        END AS percentage
    FROM meeting_teilnehmer mt
    JOIN meetings m ON mt.meeting_id = m.id AND m.is_completed = 1
    JOIN users u ON mt.user_id = u.id
    GROUP BY u.id, u.username
    ORDER BY percentage DESC, u.username ASC
";
$result_wore = mysqli_query($conn, $sql_wore_color);
while ($row = mysqli_fetch_assoc($result_wore)) {
    $attended = (int)$row['total_attendance'];
    $wore = (int)$row['wore_color_count'];
    $percentage = (float)$row['percentage'];
    $wore_color_stats[] = [
        'username' => $row['username'],
        'wore_color_count' => $wore,
        'total_attendance' => $attended,
        'percentage' => $percentage
    ];
    $wore_color_percentages[] = $percentage;
    $wore_color_attended[] = $attended;
    $wore_color_count[] = $wore;
}

// Verschiebungsvorschläge (unverändert)
$reschedule_stats = [];
$sql_reschedule = "
    SELECT u.username, COUNT(p.id) AS reschedule_count
    FROM meeting_reschedule_proposals p
    JOIN users u ON p.proposed_by_user_id = u.id
    GROUP BY u.username
    ORDER BY reschedule_count DESC, u.username ASC
";
$result_reschedule = mysqli_query($conn, $sql_reschedule);
while ($row = mysqli_fetch_assoc($result_reschedule)) {
    $reschedule_stats[] = $row;
}

require_once 'inc/header.php';
?>

<div class="container mt-5">
    <h2 class="mb-4">Statistiken & Ranking</h2>

    <div class="card shadow mb-4">
        <div class="card-header bg-primary-subtle text-secondary">
            <h4 class="mb-0">Gesamt-Statistiken</h4>
        </div>
        <div class="card-body">
            <div class="row justify-content-center">
                <div class="col-lg-6">
                    <h5 class="card-title text-center">Häufigkeit der gewählten Farben</h5>
                    <canvas id="colorChart" class="mb-4"></canvas>
                </div>
                <hr class="d-lg-none my-4">
                <div class="col-lg-6">
                    <h5 class="card-title text-center">Teilnahme-Ranking</h5>
                    <p class="text-center text-muted mt-2 mb-3">
                        Insgesamt <?= $total_meetings ?> abgeschlossene Treffen.<br>
                        Durchschnittliche Anwesenheit je Treffen: <strong><?= htmlspecialchars($avg_attendance) ?></strong>
                    </p>
                    <div style="height: 40vh;">
                        <canvas id="participationChart"></canvas>
                    </div>
                </div>
            </div>

            <hr class="my-4">

            <div class="row justify-content-center">
                <div class="col-lg-6">
                    <h5 class="card-title text-center">Ranking - Farbe getragen</h5>
                    <p class="text-center text-muted mt-2 mb-3">
                        Durchschnittliche korrekte Farbe je Treffen: <strong><?= htmlspecialchars($avg_wore_color) ?></strong>
                    </p>
                    <div style="height: 40vh;">
                        <canvas id="woreColorChart"></canvas>
                    </div>
                </div>
            </div>

            <hr class="my-4">

            <div class="row justify-content-center">
                <div class="col-lg-6">
                    <h5 class="card-title text-center">Ranking - Verschiebungsvorschläge</h5>
                    <p class="text-center text-muted mt-2 mb-3">
                        Wer schlägt am häufigsten eine Terminverschiebung vor?
                    </p>
                    <div style="height: 40vh;">
                        <canvas id="rescheduleChart"></canvas>
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>

<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
    document.addEventListener('DOMContentLoaded', function() {
        // Farben (unverändert)
        new Chart(document.getElementById('colorChart'), {
            type: 'pie',
            data: {
                labels: <?= json_encode(array_column($color_stats, 'name')) ?>,
                datasets: [{
                    label: 'Anzahl Treffen',
                    data: <?= json_encode(array_column($color_stats, 'meeting_count')) ?>,
                    backgroundColor: <?= json_encode(array_column($color_stats, 'hex_code')) ?>,
                    borderColor: 'rgba(0,0,0,0.1)',
                    borderWidth: 1
                }]
            },
            options: {
                responsive: true,
                plugins: {
                    legend: {
                        position: 'top'
                    },
                    title: {
                        display: true,
                        text: 'Verteilung der Farbwahl'
                    }
                }
            }
        });

        // Teilnahme: Mit Prozentwerten (0–100%), sortiert nach Prozent
        new Chart(document.getElementById('participationChart'), {
            type: 'bar',
            data: {
                labels: <?= json_encode(array_column($participation_stats, 'username')) ?>,
                datasets: [{
                    label: 'Teilnahmequote (%)',
                    data: <?= json_encode($participation_percentages) ?>,
                    backgroundColor: 'rgba(54,162,235,0.8)',
                    borderColor: 'rgb(54,162,235)',
                    borderWidth: 1
                }]
            },
            options: {
                indexAxis: 'y',
                responsive: true,
                maintainAspectRatio: false,
                plugins: {
                    legend: {
                        display: false
                    },
                    title: {
                        display: true,
                        text: 'Teilnahmequote (nur abgeschlossene Treffen mit Eintrag)'
                    },
                    tooltip: {
                        callbacks: {
                            label: function(ctx) {
                                const pct = ctx.raw;
                                const attended = <?= json_encode($participation_attended) ?>[ctx.dataIndex] || 0;
                                const reg = <?= json_encode($participation_registered) ?>[ctx.dataIndex] || 0;
                                return `${attended} von ${reg} (${pct}%)`;
                            }
                        }
                    }
                },
                scales: {
                    x: {
                        beginAtZero: true,
                        max: 100,
                        ticks: {
                            callback: function(value) {
                                return value + '%';
                            }
                        }
                    },
                    y: {
                        ticks: {
                            font: {
                                size: 10
                            },
                            callback: function(v) {
                                const l = this.getLabelForValue(v);
                                return l.length > 15 ? l.substring(0, 15) + '...' : l;
                            }
                        }
                    }
                }
            }
        });

        // Farbe getragen: Mit Prozentwerten (0–100%)
        new Chart(document.getElementById('woreColorChart'), {
            type: 'bar',
            data: {
                labels: <?= json_encode(array_column($wore_color_stats, 'username')) ?>,
                datasets: [{
                    label: 'Farbenquote (%)',
                    data: <?= json_encode($wore_color_percentages) ?>,
                    backgroundColor: 'rgba(75,192,192,0.8)',
                    borderColor: 'rgb(75,192,192)',
                    borderWidth: 1
                }]
            },
            options: {
                indexAxis: 'y',
                responsive: true,
                maintainAspectRatio: false,
                plugins: {
                    legend: {
                        display: false
                    },
                    title: {
                        display: true,
                        text: 'Farbenquote (nur bei Teilnahme)'
                    },
                    tooltip: {
                        callbacks: {
                            label: function(ctx) {
                                const pct = ctx.raw;
                                const wore = <?= json_encode($wore_color_count) ?>[ctx.dataIndex] || 0;
                                const att = <?= json_encode($wore_color_attended) ?>[ctx.dataIndex] || 0;
                                return `${wore} von ${att} Teilnahmen (${pct}%)`;
                            }
                        }
                    }
                },
                scales: {
                    x: {
                        beginAtZero: true,
                        max: 100,
                        ticks: {
                            callback: function(value) {
                                return value + '%';
                            }
                        }
                    },
                    y: {
                        ticks: {
                            font: {
                                size: 10
                            },
                            callback: function(v) {
                                const l = this.getLabelForValue(v);
                                return l.length > 15 ? l.substring(0, 15) + '...' : l;
                            }
                        }
                    }
                }
            }
        });

        // Verschiebungsvorschläge (unverändert – ABER mit korrekter Syntax!)
        new Chart(document.getElementById('rescheduleChart'), {
            type: 'bar',
            data: {
                labels: <?= json_encode(array_column($reschedule_stats, 'username')) ?>,
                datasets: [{
                    label: 'Vorschläge',
                    data: <?= json_encode(array_column($reschedule_stats, 'reschedule_count')) ?>,
                    backgroundColor: 'rgba(255,159,64,0.8)',
                    borderColor: 'rgb(255,159,64)',
                    borderWidth: 1
                }]
            },
            options: {
                indexAxis: 'y',
                responsive: true,
                maintainAspectRatio: false,
                plugins: {
                    legend: {
                        display: false
                    },
                    title: {
                        display: true,
                        text: 'Verschiebungsvorschläge'
                    }
                },
                scales: {
                    x: {
                        beginAtZero: true,
                        ticks: {
                            stepSize: 1
                        }
                    },
                    y: {
                        ticks: {
                            font: {
                                size: 10
                            },
                            callback: function(v) {
                                const l = this.getLabelForValue(v);
                                return l.length > 15 ? l.substring(0, 15) + '...' : l;
                            }
                        }
                    }
                }
            }
        });
    });
</script>

<?php include('inc/footer.php'); ?>