index.php

<?php
require_once("includes/auth.php");
require_role(['admin', 'staff']);
include("config.php");
$page_title = "Dashboard";
include("includes/header.php");

// สถิติทั้งหมด
$sql_total_students = "SELECT COUNT(*) as total FROM students";
$result_students = mysqli_query($conn, $sql_total_students);
$total_students = mysqli_fetch_assoc($result_students)['total'];

$sql_total_companies = "SELECT COUNT(*) as total FROM companies";
$result_companies = mysqli_query($conn, $sql_total_companies);
$total_companies = mysqli_fetch_assoc($result_companies)['total'];

$sql_ongoing = "SELECT COUNT(*) as total FROM internships WHERE status = 'กำลังฝึกงาน'";
$result_ongoing = mysqli_query($conn, $sql_ongoing);
$total_ongoing = mysqli_fetch_assoc($result_ongoing)['total'];

$sql_completed = "SELECT COUNT(*) as total FROM internships WHERE status = 'ฝึกงานเสร็จสิ้น'";
$result_completed = mysqli_query($conn, $sql_completed);
$total_completed = mysqli_fetch_assoc($result_completed)['total'];

$sql_cancelled = "SELECT COUNT(*) as total FROM internships WHERE status = 'ยกเลิกการฝึกงาน'";
$result_cancelled = mysqli_query($conn, $sql_cancelled);
$total_cancelled = mysqli_fetch_assoc($result_cancelled)['total'];

// สถิติบัญชีนักศึกษา (workflow อนุมัติ)
$sql_accounts = "SELECT
    SUM(CASE WHEN u.account_status = 'pending' THEN 1 ELSE 0 END) AS pending_count,
    SUM(CASE WHEN u.account_status = 'approved' THEN 1 ELSE 0 END) AS approved_count,
    SUM(CASE WHEN u.account_status = 'rejected' THEN 1 ELSE 0 END) AS rejected_count,
    SUM(CASE WHEN u.account_status = 'suspended' THEN 1 ELSE 0 END) AS suspended_count
    FROM users u WHERE u.role = 'student'";
$account_stats = mysqli_fetch_assoc(mysqli_query($conn, $sql_accounts));

$sql_profile = "SELECT
    SUM(CASE WHEN profile_completed = 1 THEN 1 ELSE 0 END) AS completed_count,
    SUM(CASE WHEN profile_completed = 0 THEN 1 ELSE 0 END) AS incomplete_count
    FROM students";
$profile_stats = mysqli_fetch_assoc(mysqli_query($conn, $sql_profile));

$sql_placement = "SELECT
    (SELECT COUNT(DISTINCT student_id) FROM internships) AS has_internship,
    (SELECT COUNT(*) FROM students s WHERE NOT EXISTS (SELECT 1 FROM internships i WHERE i.student_id = s.student_id)) AS no_internship";
$placement_stats = mysqli_fetch_assoc(mysqli_query($conn, $sql_placement));

// นักศึกษารออนุมัติล่าสุด 5 คน
$sql_recent_pending = "SELECT u.user_id, u.full_name, u.created_at, s.student_id, s.program
    FROM users u JOIN students s ON s.user_id = u.user_id
    WHERE u.role = 'student' AND u.account_status = 'pending'
    ORDER BY u.created_at ASC LIMIT 5";
$recent_pending = mysqli_query($conn, $sql_recent_pending);
?>

<div class="page-header">
    <div>
        <h1>ภาพรวมระบบ</h1>
        <p class="page-subtitle">สรุปสถานะนักศึกษาและการฝึกงานทั้งหมด</p>
    </div>
</div>

<div class="stat-grid">
    <div class="stat-card">
        <div class="stat-icon"><?php echo icon('users'); ?></div>
        <div class="stat-value"><?php echo number_format($total_students); ?></div>
        <div class="stat-label">นักศึกษาทั้งหมด</div>
    </div>
    <div class="stat-card stat-warning">
        <div class="stat-icon"><?php echo icon('clock'); ?></div>
        <div class="stat-value"><?php echo number_format($account_stats['pending_count'] ?? 0); ?></div>
        <div class="stat-label">รออนุมัติ</div>
        <a href="student_approvals.php" class="stat-link">ไปยังหน้ารออนุมัติ</a>
    </div>
    <div class="stat-card stat-success">
        <div class="stat-icon"><?php echo icon('building'); ?></div>
        <div class="stat-value"><?php echo number_format($total_companies); ?></div>
        <div class="stat-label">สถานประกอบการทั้งหมด</div>
    </div>
    <div class="stat-card stat-success">
        <div class="stat-icon"><?php echo icon('briefcase'); ?></div>
        <div class="stat-value"><?php echo number_format($placement_stats['has_internship'] ?? 0); ?></div>
        <div class="stat-label">ได้สถานที่ฝึกงานแล้ว</div>
    </div>
</div>

<div class="row">
    <div class="col-6">
        <div class="card" style="height:100%;">
            <div class="card-header">
                <h3><?php echo icon('clock', ['class'=>'icon-sm']); ?> นักศึกษารออนุมัติล่าสุด</h3>
                <a href="student_approvals.php" class="btn btn-secondary btn-sm">ดูทั้งหมด</a>
            </div>
            <?php if(mysqli_num_rows($recent_pending) > 0): ?>
                <div class="detail-list">
                    <?php while($p = mysqli_fetch_assoc($recent_pending)): ?>
                    <div class="detail-row">
                        <dt><?php echo htmlspecialchars($p['student_id']); ?></dt>
                        <dd>
                            <?php echo htmlspecialchars($p['full_name']); ?>
                            <span class="text-muted fs-helper"> &middot; <?php echo htmlspecialchars($p['program'] ?? '-'); ?></span>
                        </dd>
                    </div>
                    <?php endwhile; ?>
                </div>
            <?php else: ?>
                <div class="empty-state" style="padding: var(--space-6);">
                    <?php echo icon('check-circle', ['class'=>'icon-lg']); ?>
                    <p>ไม่มีนักศึกษารออนุมัติในขณะนี้</p>
                </div>
            <?php endif; ?>
        </div>
    </div>

    <div class="col-6">
        <div class="card" style="height:100%;">
            <div class="card-header"><h3><?php echo icon('list', ['class'=>'icon-sm']) ?: icon('file-text', ['class'=>'icon-sm']); ?> สถานะโดยรวม</h3></div>
            <div class="detail-list">
                <div class="detail-row"><dt>อนุมัติแล้ว</dt><dd><span class="badge badge-success"><?php echo number_format($account_stats['approved_count'] ?? 0); ?></span></dd></div>
                <div class="detail-row"><dt>ไม่อนุมัติ</dt><dd><span class="badge badge-danger"><?php echo number_format($account_stats['rejected_count'] ?? 0); ?></span></dd></div>
                <div class="detail-row"><dt>ระงับการใช้งาน</dt><dd><span class="badge badge-neutral"><?php echo number_format($account_stats['suspended_count'] ?? 0); ?></span></dd></div>
                <div class="detail-row"><dt>ข้อมูลส่วนตัวยังไม่ครบ</dt><dd><span class="badge badge-warning"><?php echo number_format($profile_stats['incomplete_count'] ?? 0); ?></span></dd></div>
                <div class="detail-row"><dt>ยังไม่มีสถานที่ฝึกงาน</dt><dd><span class="badge badge-warning"><?php echo number_format($placement_stats['no_internship'] ?? 0); ?></span></dd></div>
                <div class="detail-row"><dt>กำลังฝึกงาน</dt><dd><span class="badge badge-info"><?php echo number_format($total_ongoing); ?></span></dd></div>
                <div class="detail-row"><dt>ฝึกงานเสร็จสิ้น</dt><dd><span class="badge badge-success"><?php echo number_format($total_completed); ?></span></dd></div>
                <div class="detail-row"><dt>ยกเลิกการฝึกงาน</dt><dd><span class="badge badge-danger"><?php echo number_format($total_cancelled); ?></span></dd></div>
            </div>
        </div>
    </div>
</div>

<h2 class="section-title">อันดับยอดนิยม</h2>
<div class="row">
    <div class="col-6">
        <div class="card" style="height:100%;">
            <div class="card-header"><h3>จังหวัดยอดนิยม</h3></div>
            <?php
            $sql = "SELECT c.province, COUNT(i.internship_id) as count
                    FROM companies c JOIN internships i ON c.company_id = i.company_id
                    GROUP BY c.province ORDER BY count DESC LIMIT 5";
            $result = mysqli_query($conn, $sql);
            if(mysqli_num_rows($result) > 0):
                $rank = 1;
                while($row = mysqli_fetch_assoc($result)):
            ?>
            <a href="province_details.php?province=<?php echo urlencode($row['province']); ?>" class="detail-row" style="text-decoration:none; display:grid;">
                <dt><?php echo $rank++; ?>. <?php echo icon('map-pin', ['class'=>'icon-sm']); ?> <?php echo htmlspecialchars($row['province']); ?></dt>
                <dd><span class="badge badge-info"><?php echo number_format($row['count']); ?> คน</span></dd>
            </a>
            <?php
                endwhile;
            else:
            ?>
            <div class="empty-state" style="padding: var(--space-6);">
                <?php echo icon('map-pin', ['class'=>'icon-lg']); ?>
                <p>ยังไม่มีข้อมูลการฝึกงาน</p>
            </div>
            <?php endif; ?>
        </div>
    </div>

    <div class="col-6">
        <div class="card" style="height:100%;">
            <div class="card-header"><h3>สถานประกอบการยอดนิยม</h3></div>
            <?php
            $sql = "SELECT c.company_name, c.company_id, c.province, COUNT(i.internship_id) as count
                    FROM companies c JOIN internships i ON c.company_id = i.company_id
                    GROUP BY c.company_id ORDER BY count DESC LIMIT 5";
            $result = mysqli_query($conn, $sql);
            if(mysqli_num_rows($result) > 0):
                $rank = 1;
                while($row = mysqli_fetch_assoc($result)):
            ?>
            <a href="company_details.php?id=<?php echo $row['company_id']; ?>" class="detail-row" style="text-decoration:none; display:grid;">
                <dt><?php echo $rank++; ?>. <?php echo icon('building', ['class'=>'icon-sm']); ?> <?php echo htmlspecialchars($row['company_name']); ?></dt>
                <dd><span class="badge badge-success"><?php echo number_format($row['count']); ?> คน</span></dd>
            </a>
            <?php
                endwhile;
            else:
            ?>
            <div class="empty-state" style="padding: var(--space-6);">
                <?php echo icon('building', ['class'=>'icon-lg']); ?>
                <p>ยังไม่มีข้อมูลการฝึกงาน</p>
            </div>
            <?php endif; ?>
        </div>
    </div>
</div>

<?php include("includes/footer.php"); ?>