<?php
include("../session.php");

// ตรวจสอบสิทธิ์ admin
if ($role != 'admin') {
    header("Location: ../index.php");
    exit;
}

include("../db.php");

// สถิติทั่วไป
$stats = array(
    'total_users' => 0,
    'total_groups' => 0,
    'total_subjects' => 0,
    'total_questions' => 0,
    'total_exams' => 0,
    'pass_rate' => 0,
    'avg_score' => 0,
    'curriculum_2565_2567' => array('total' => 0, 'passed' => 0),
    'curriculum_2568' => array('total' => 0, 'passed' => 0)
);

// จำนวนผู้ใช้ (เฉพาะ role = 'user')
$result = mysqli_query($conn, "SELECT COUNT(*) as count FROM users WHERE role = 'user'");
if ($row = mysqli_fetch_assoc($result)) {
    $stats['total_users'] = $row['count'];
}

// จำนวนกลุ่ม
$result = mysqli_query($conn, "SELECT COUNT(*) as count FROM groups");
if ($row = mysqli_fetch_assoc($result)) {
    $stats['total_groups'] = $row['count'];
}

// จำนวนวิชา
$result = mysqli_query($conn, "SELECT COUNT(*) as count FROM subjects");
if ($row = mysqli_fetch_assoc($result)) {
    $stats['total_subjects'] = $row['count'];
}

// จำนวนคำถามทั้งหมด
$result = mysqli_query($conn, "SELECT COUNT(*) as count FROM questions");
if ($row = mysqli_fetch_assoc($result)) {
    $stats['total_questions'] = $row['count'];
}

// จำนวนการสอบทั้งหมด และแยกตามหลักสูตร
$result = mysqli_query($conn, "SELECT COUNT(*) as count, s.curriculum_year 
    FROM exam_results e 
    JOIN subjects s ON e.subject_id = s.id 
    GROUP BY s.curriculum_year");
while ($row = mysqli_fetch_assoc($result)) {
    $stats['total_exams'] += $row['count'];
    if ($row['curriculum_year'] == '2565-2567') {
        $stats['curriculum_2565_2567']['total'] = $row['count'];
    } elseif ($row['curriculum_year'] == '2568') {
        $stats['curriculum_2568']['total'] = $row['count'];
    }
}

// อัตราการสอบผ่านรวม และแยกตามหลักสูตร
if ($stats['total_exams'] > 0) {
    $result = mysqli_query($conn, "SELECT COUNT(*) as count, s.curriculum_year 
        FROM exam_results e 
        JOIN subjects s ON e.subject_id = s.id 
        WHERE e.passed = 1 
        GROUP BY s.curriculum_year");
    while ($row = mysqli_fetch_assoc($result)) {
        if ($row['curriculum_year'] == '2565-2567') {
            $stats['curriculum_2565_2567']['passed'] = $row['count'];
        } elseif ($row['curriculum_year'] == '2568') {
            $stats['curriculum_2568']['passed'] = $row['count'];
        }
    }
    $passed_total = $stats['curriculum_2565_2567']['passed'] + $stats['curriculum_2568']['passed'];
    $stats['pass_rate'] = round(($passed_total / $stats['total_exams']) * 100, 2);
}

// คะแนนเฉลี่ย
$result = mysqli_query($conn, "SELECT AVG(percentage) as avg_score FROM exam_results");
if ($row = mysqli_fetch_assoc($result)) {
    $stats['avg_score'] = round(isset($row['avg_score']) && $row['avg_score'] !== null ? $row['avg_score'] : 0, 2);
}

// สถิติการสอบแต่ละวิชา
$subject_stats = array();
$result = mysqli_query($conn, "SELECT s.id, s.name, s.curriculum_year, g.group_name 
    FROM subjects s 
    LEFT JOIN groups g ON s.group_id = g.user_id 
    ORDER BY s.name");
while ($subject = mysqli_fetch_assoc($result)) {
    $subject_id = $subject['id'];
    
    // จำนวนการสอบในวิชานี้
    $res = mysqli_query($conn, "SELECT COUNT(*) as count FROM exam_results WHERE subject_id = $subject_id");
    $exam_count = mysqli_fetch_assoc($res)['count'];
    
    // จำนวนผู้สอบผ่าน
    $res = mysqli_query($conn, "SELECT COUNT(*) as count FROM exam_results WHERE subject_id = $subject_id AND passed = 1");
    $pass_count = mysqli_fetch_assoc($res)['count'];
    
    // อัตราการผ่าน
    $pass_rate = $exam_count > 0 ? round(($pass_count / $exam_count) * 100, 2) : 0;
    
    // คะแนนเฉลี่ย
    $res = mysqli_query($conn, "SELECT AVG(percentage) as avg FROM exam_results WHERE subject_id = $subject_id");
    $row = mysqli_fetch_assoc($res);
    $avg_score = round(isset($row['avg']) && $row['avg'] !== null ? $row['avg'] : 0, 2);
    
    // จำนวนข้อสอบในวิชานี้
    $res = mysqli_query($conn, "SELECT COUNT(*) as count FROM questions WHERE subject_id = $subject_id");
    $question_count = mysqli_fetch_assoc($res)['count'];
    
    $subject_stats[] = array(
        'id' => $subject_id,
        'name' => $subject['name'],
        'curriculum_year' => $subject['curriculum_year'],
        'group_name' => isset($subject['group_name']) ? $subject['group_name'] : 'ไม่มีกลุ่ม',
        'exams' => $exam_count,
        'pass_count' => $pass_count,
        'pass_rate' => $pass_rate,
        'avg_score' => $avg_score,
        'questions' => $question_count
    );
}

// สถิติการสอบล่าสุด (10 รายการ)
$recent_exams = array();
$result = mysqli_query($conn, "
    SELECT e.*, u.username, u.u_name, s.name as subject_name, s.curriculum_year, g.group_name
    FROM exam_results e
    JOIN users u ON e.user_id = u.id
    JOIN subjects s ON e.subject_id = s.id
    LEFT JOIN groups g ON s.group_id = g.id
    ORDER BY e.test_date DESC
    LIMIT 10
");
while ($row = mysqli_fetch_assoc($result)) {
    $recent_exams[] = $row;
}

// ผู้ใช้ที่มีกิจกรรมมากที่สุด (5 อันดับแรก)
$active_users = array();
$result = mysqli_query($conn, "
    SELECT u.id, u.username, u.u_name, COUNT(e.id) as exam_count 
    FROM users u
    JOIN exam_results e ON u.id = e.user_id
    GROUP BY u.id
    ORDER BY exam_count DESC
    LIMIT 5
");
while ($row = mysqli_fetch_assoc($result)) {
    $active_users[] = $row;
}

// สถิติตามกลุ่ม
$group_stats = array();
$result = mysqli_query($conn, "SELECT g.id, g.group_name 
    FROM groups g 
    ORDER BY g.group_name");
while ($group = mysqli_fetch_assoc($result)) {
    $group_id = $group['id'];
    
    // จำนวนวิชาในกลุ่ม
    $res = mysqli_query($conn, "SELECT COUNT(*) as count FROM subjects WHERE group_id = $group_id");
    $subject_count = mysqli_fetch_assoc($res)['count'];
    
    // จำนวนการสอบในกลุ่ม
    $res = mysqli_query($conn, "SELECT COUNT(*) as count 
        FROM exam_results e 
        JOIN subjects s ON e.subject_id = s.id 
        WHERE s.group_id = $group_id");
    $exam_count = mysqli_fetch_assoc($res)['count'];
    
    // อัตราการผ่าน
    $res = mysqli_query($conn, "SELECT COUNT(*) as count 
        FROM exam_results e 
        JOIN subjects s ON e.subject_id = s.id 
        WHERE s.group_id = $group_id AND e.passed = 1");
    $pass_count = mysqli_fetch_assoc($res)['count'];
    $pass_rate = $exam_count > 0 ? round(($pass_count / $exam_count) * 100, 2) : 0;
    
    $group_stats[] = array(
        'id' => $group_id,
        'name' => $group['group_name'],
        'subjects' => $subject_count,
        'exams' => $exam_count,
        'pass_rate' => $pass_rate
    );
}
?>

<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>สถิติการใช้งานระบบ</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
    <script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.9.4/Chart.min.js"></script>
    <style>
        body { padding-top: 20px; }
        .stat-box {
            text-align: center;
            padding: 15px;
            margin-bottom: 20px;
            border-radius: 5px;
            color: white;
        }
        .stat-box h4 { font-size: 16px; margin-top: 0; }
        .stat-box .number { font-size: 24px; font-weight: bold; }
        .bg-primary { background-color: #337ab7; }
        .bg-success { background-color: #5cb85c; }
        .bg-info { background-color: #5bc0de; }
        .bg-warning { background-color: #f0ad4e; }
        .bg-purple { background-color: #800080; }
        .panel { margin-top: 20px; }
        .chart-container { height: 300px; }
        .curriculum-summary { background-color: #f9f9f9; padding: 15px; border-radius: 4px; }
    </style>
</head>
<body>
<div class="container">
    <h3 class="text-center">สถิติการใช้งานระบบ</h3>
    
    <p class="text-right">
        <a href="dashboard.php" class="btn btn-default btn-sm">← กลับหน้าหลัก</a>
    </p>
    
    <!-- สถิติภาพรวม -->
    <div class="row">
        <div class="col-md-3">
            <div class="stat-box bg-primary">
                <h4>จำนวนผู้ใช้</h4>
                <div class="number"><?php echo $stats['total_users']; ?></div>
            </div>
        </div>
        <div class="col-md-3">
            <div class="stat-box bg-success">
                <h4>จำนวนกลุ่ม</h4>
                <div class="number"><?php echo $stats['total_groups']; ?></div>
            </div>
        </div>
        <div class="col-md-3">
            <div class="stat-box bg-info">
                <h4>จำนวนวิชา</h4>
                <div class="number"><?php echo $stats['total_subjects']; ?></div>
            </div>
        </div>
        <div class="col-md-3">
            <div class="stat-box bg-warning">
                <h4>จำนวนการสอบ</h4>
                <div class="number"><?php echo $stats['total_exams']; ?></div>
            </div>
        </div>
    </div>
    
    <!-- สรุปตามหลักสูตร -->
    <div class="curriculum-summary">
        <h4>สรุปตามหลักสูตร</h4>
        <div class="row">
            <div class="col-md-6">
                <div class="stat-box bg-purple">
                    <h4>หลักสูตร 2565-2567</h4>
                    <div class="number"><?php echo $stats['curriculum_2565_2567']['total']; ?> การสอบ</div>
                    <div>ผ่าน: <?php echo $stats['curriculum_2565_2567']['passed']; ?> (<?php echo $stats['curriculum_2565_2567']['total'] > 0 ? round(($stats['curriculum_2565_2567']['passed'] / $stats['curriculum_2565_2567']['total']) * 100, 2) : 0; ?>%)</div>
                </div>
            </div>
            <div class="col-md-6">
                <div class="stat-box bg-purple">
                    <h4>หลักสูตร 2568</h4>
                    <div class="number"><?php echo $stats['curriculum_2568']['total']; ?> การสอบ</div>
                    <div>ผ่าน: <?php echo $stats['curriculum_2568']['passed']; ?> (<?php echo $stats['curriculum_2568']['total'] > 0 ? round(($stats['curriculum_2568']['passed'] / $stats['curriculum_2568']['total']) * 100, 2) : 0; ?>%)</div>
                </div>
            </div>
        </div>
    </div>
    
    <div class="row">
        <div class="col-md-6">
            <div class="panel panel-default">
                <div class="panel-heading">อัตราการสอบผ่านรวม</div>
                <div class="panel-body">
                    <div class="chart-container">
                        <canvas id="passRateChart"></canvas>
                    </div>
                </div>
            </div>
        </div>
        <div class="col-md-6">
            <div class="panel panel-default">
                <div class="panel-heading">สถิติการสอบตามกลุ่ม</div>
                <div class="panel-body">
                    <div class="chart-container">
                        <canvas id="groupStatsChart"></canvas>
                    </div>
                </div>
            </div>
        </div>
    </div>
    
    <!-- สถิติแยกตามวิชา -->
    <div class="panel panel-default">
        <div class="panel-heading">รายละเอียดสถิติแต่ละวิชา</div>
        <div class="panel-body">
            <table class="table table-bordered table-striped">
                <thead>
                    <tr>
                        <th>วิชา</th>
                        <th>หลักสูตร</th>
                        <th>กลุ่ม</th>
                        <th>จำนวนข้อสอบ</th>
                        <th>จำนวนการสอบ</th>
                        <th>จำนวนผ่าน</th>
                        <th>อัตราการผ่าน</th>
                        <th>คะแนนเฉลี่ย</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($subject_stats as $subject): ?>
                    <tr>
                        <td><?php echo htmlspecialchars($subject['name']); ?></td>
                        <td><?php echo htmlspecialchars($subject['curriculum_year']); ?></td>
                        <td><?php echo htmlspecialchars($subject['group_name']); ?></td>
                        <td><?php echo $subject['questions']; ?></td>
                        <td><?php echo $subject['exams']; ?></td>
                        <td><?php echo $subject['pass_count']; ?></td>
                        <td><?php echo $subject['pass_rate']; ?>%</td>
                        <td><?php echo $subject['avg_score']; ?>%</td>
                    </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>
    </div>
    
    <!-- สถิติตามกลุ่ม -->
    <div class="panel panel-default">
        <div class="panel-heading">สถิติตามกลุ่ม</div>
        <div class="panel-body">
            <table class="table table-bordered table-striped">
                <thead>
                    <tr>
                        <th>กลุ่ม</th>
                        <th>จำนวนวิชา</th>
                        <th>จำนวนการสอบ</th>
                        <th>อัตราการผ่าน</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($group_stats as $group): ?>
                    <tr>
                        <td><?php echo htmlspecialchars($group['name']); ?></td>
                        <td><?php echo $group['subjects']; ?></td>
                        <td><?php echo $group['exams']; ?></td>
                        <td><?php echo $group['pass_rate']; ?>%</td>
                    </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>
    </div>
    
    <!-- การสอบล่าสุด -->
    <div class="panel panel-default">
        <div class="panel-heading">การสอบล่าสุด</div>
        <div class="panel-body">
            <table class="table table-bordered">
                <thead>
                    <tr>
                        <th>วันที่</th>
                        <th>ผู้ใช้</th>
                        <th>วิชา</th>
                        <th>หลักสูตร</th>
                        <th>กลุ่ม</th>
                        <th>คะแนน</th>
                        <th>เปอร์เซ็นต์</th>
                        <th>ผลสอบ</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($recent_exams as $exam): ?>
                    <tr>
                        <td><?php echo date('d/m/Y H:i', strtotime($exam['test_date'])); ?></td>
                        <td><?php echo htmlspecialchars($exam['username']); ?> (<?php echo htmlspecialchars($exam['u_name']); ?>)</td>
                        <td><?php echo htmlspecialchars($exam['subject_name']); ?></td>
                        <td><?php echo htmlspecialchars($exam['curriculum_year']); ?></td>
                        <td><?php echo htmlspecialchars(isset($exam['group_name']) ? $exam['group_name'] : 'ไม่มีกลุ่ม'); ?></td>
                        <td><?php echo $exam['score']; ?></td>
                        <td><?php echo $exam['percentage']; ?>%</td>
                        <td>
                            <?php if ($exam['passed']): ?>
                                <span class="label label-success">ผ่าน</span>
                            <?php else: ?>
                                <span class="label label-danger">ไม่ผ่าน</span>
                            <?php endif; ?>
                        </td>
                    </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>
    </div>
    
    <!-- ผู้ใช้ที่มีกิจกรรมมากที่สุด -->
    <div class="panel panel-default">
        <div class="panel-heading">ผู้ใช้ที่มีกิจกรรมมากที่สุด</div>
        <div class="panel-body">
            <table class="table table-bordered">
                <thead>
                    <tr>
                        <th>ผู้ใช้</th>
                        <th>จำนวนการสอบ</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($active_users as $user): ?>
                    <tr>
                        <td><?php echo htmlspecialchars($user['username']); ?> (<?php echo htmlspecialchars($user['u_name']); ?>)</td>
                        <td><?php echo $user['exam_count']; ?></td>
                    </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>
    </div>
</div>

<script>
// กราฟแสดงอัตราการสอบผ่าน
var passRateCtx = document.getElementById('passRateChart').getContext('2d');
var passRateChart = new Chart(passRateCtx, {
    type: 'pie',
    data: {
        labels: ['ผ่าน', 'ไม่ผ่าน'],
        datasets: [{
            data: [
                <?php echo $stats['pass_rate']; ?>,
                <?php echo 100 - $stats['pass_rate']; ?>
            ],
            backgroundColor: ['#5cb85c', '#d9534f']
        }]
    },
    options: {
        responsive: true,
        maintainAspectRatio: false,
        title: {
            display: true,
            text: 'อัตราการสอบผ่านรวม: <?php echo $stats['pass_rate']; ?>%'
        }
    }
});

// กราฟแสดงสถิติตามกลุ่ม
var groupStatsCtx = document.getElementById('groupStatsChart').getContext('2d');
var groupStatsChart = new Chart(groupStatsCtx, {
    type: 'bar',
    data: {
        labels: [
            <?php
            $labels = array();
            foreach ($group_stats as $group) {
                $labels[] = '"' . addslashes($group['name']) . '"';
            }
            echo implode(',', $labels);
            ?>
        ],
        datasets: [{
            label: 'จำนวนการสอบ',
            data: [
                <?php
                $exams = array();
                foreach ($group_stats as $group) {
                    $exams[] = $group['exams'];
                }
                echo implode(',', $exams);
                ?>
            ],
            backgroundColor: '#337ab7'
        }, {
            label: 'อัตราการผ่าน (%)',
            data: [
                <?php
                $pass_rates = array();
                foreach ($group_stats as $group) {
                    $pass_rates[] = $group['pass_rate'];
                }
                echo implode(',', $pass_rates);
                ?>
            ],
            backgroundColor: '#5cb85c'
        }]
    },
    options: {
        responsive: true,
        maintainAspectRatio: false,
        scales: {
            yAxes: [{
                ticks: {
                    beginAtZero: true
                }
            }]
        }
    }
});
</script>
</body>
</html>