<?php include 'header.php'; ?>

<?php
if (session_id() == '') {
    session_start();
}
include '../inc/connect.php';

// แจ้งเตือนนัดหมายติดตามเพียงครั้งเดียวต่อ session การ login
$show_followup_alert = !isset($_SESSION['follow_up_notified']);
if ($show_followup_alert) {
    $_SESSION['follow_up_notified'] = true;
}

function safe_json($data, $fallback = '[]')
{
    $json = json_encode($data);
    return $json !== false ? $json : $fallback;
}

$raw_school_id = isset($schoolID) ? $schoolID : (isset($_SESSION['schoolID']) ? $_SESSION['schoolID'] : '');
$raw_school_id = preg_replace('/[^a-zA-Z0-9_]/', '', $raw_school_id);
$current_user_type = isset($user_type_id) ? intval($user_type_id) : (isset($_SESSION['user_type_id']) ? intval($_SESSION['user_type_id']) : 0);
$is_admin = ($current_user_type === 9);

$show_search_box = true;
$search_box_label = "ค้นหา";

if ($is_admin) {
    $user_school_id = '';
    $role_name = "ส่วนกลาง (สิทธิ์เต็ม)";
    $group_col = "s.province";
    $chart_label = "จังหวัด";
    $search_box_label = "ค้นหาชื่อจังหวัด";
} else {
    $user_school_id = $raw_school_id;
    if ($current_user_type === 1) {
        $role_name = "จังหวัด";
        $group_col = "s.amphur";
        $chart_label = "อำเภอ";
        $search_box_label = "ค้นหาชื่ออำเภอ";
    } else if ($current_user_type === 2) {
        $role_name = "อำเภอ";
        $group_col = "s.firstname";
        $chart_label = "สถานศึกษา";
        $search_box_label = "ค้นหาชื่อตำบล";
    } else if ($current_user_type === 3) {
        $role_name = "สถานศึกษาขึ้นตรง";
        $group_col = "s.firstname";
        $chart_label = "สถานศึกษา";
        $search_box_label = "ค้นหาชื่อสถานศึกษา";
    } else if ($current_user_type === 4) {
        $role_name = "ตำบล";
        $group_col = "s.firstname";
        $chart_label = "สถานศึกษา";
        $show_search_box = false;
    } else {
        $role_name = "ไม่ระบุสิทธิ์";
        $group_col = "s.firstname";
        $chart_label = "สถานศึกษา";
    }
}

$start_date = isset($_POST['start_date']) ? $_POST['start_date'] : '';
$end_date = isset($_POST['end_date']) ? $_POST['end_date'] : '';
$service_type = isset($_POST['service_type']) ? $_POST['service_type'] : '';
$search_term = isset($_POST['search_term']) ? trim($_POST['search_term']) : '';

$where_conditions = array();
$params = array();
if ($user_school_id !== '' && $user_school_id !== '0') {
    if ($current_user_type === 1) {
        $where_conditions[] = "g.schoolID LIKE ?";
        $params[] = '%' . substr($user_school_id, 0, 4) . '%';
    } else if ($current_user_type === 2) {
        $where_conditions[] = "g.schoolID LIKE ?";
        $params[] = '%' . substr($user_school_id, 0, 6) . '%';
    } else {
        $where_conditions[] = "g.schoolID LIKE ?";
        $params[] = '%' . $user_school_id . '%';
    }
}
if (!empty($start_date)) {
    $where_conditions[] = "g.created_at >= ?";
    $params[] = $start_date . ' 00:00:00';
}
if (!empty($end_date)) {
    $where_conditions[] = "g.created_at <= ?";
    $params[] = $end_date . ' 23:59:59';
}
if (!empty($service_type)) {
    $where_conditions[] = "g.guidance_type = ?";
    $params[] = $service_type;
}
if (!empty($search_term)) {
    $where_conditions[] = "(s.firstname LIKE ? OR s.province LIKE ? OR s.amphur LIKE ?)";
    $search_param = '%' . $search_term . '%';
    $params[] = $search_param;
    $params[] = $search_param;
    $params[] = $search_param;
}
$where_clause = '';
if (!empty($where_conditions)) {
    $where_clause = ' AND ' . implode(' AND ', $where_conditions);
}
$filter_params = $params;

$db_error_message = '';

try {
    if (!isset($pdo) || !$pdo) {
        throw new PDOException("ไม่สามารถเชื่อมต่อฐานข้อมูลได้");
    }
    $has_school_table = false;
    try {
        $check_school = $pdo->query("SHOW TABLES LIKE 'school'");
        $has_school_table = ($check_school && $check_school->rowCount() > 0);
    } catch (Exception $e) {
        $has_school_table = false;
    }

    // ดึงชื่อประเภทจาก typeActivity
    $stmt_ta = $pdo->query("SELECT id, title FROM typeActivity ORDER BY id ASC");
    $ta_rows = $stmt_ta->fetchAll(PDO::FETCH_ASSOC);
    $ta_titles = array(1 => 'ตลอดชีวิต', 2 => 'พัฒนาตนเอง', 3 => 'คุณวุฒิฯ', 4 => 'ประชาชน');
    if ($ta_rows) {
        foreach ($ta_rows as $r) {
            $ta_titles[$r['id']] = $r['title'];
        }
    }

    if ($has_school_table) {
        $sql = "SELECT COUNT(g.id) as total_events, SUM(g.participant_count) as total_participants FROM group_guidance g LEFT JOIN school s ON g.schoolID = s.schoolID WHERE 1=1" . $where_clause;
    } else {
        $sql = "SELECT COUNT(g.id) as total_events, SUM(g.participant_count) as total_participants FROM group_guidance g WHERE 1=1" . str_replace(array("s.firstname", "s.province", "s.amphur"), array("''", "''", "''"), $where_clause);
    }
    $stmt = $pdo->prepare($sql);
    $stmt->execute($filter_params);
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    $totalEvents = $result ? (empty($result['total_events']) ? 0 : $result['total_events']) : 0;
    $totalParticipants = $result ? (empty($result['total_participants']) ? 0 : $result['total_participants']) : 0;

    if ($has_school_table) {
        $sql = "SELECT t.title as type_name, SUM(g.participant_count) as count FROM group_guidance g LEFT JOIN school s ON g.schoolID = s.schoolID LEFT JOIN typeActivity t ON g.participant_type = t.id WHERE g.participant_type IS NOT NULL" . $where_clause . " GROUP BY g.participant_type, t.title";
    } else {
        $sql = "SELECT t.title as type_name, SUM(g.participant_count) as count FROM group_guidance g LEFT JOIN typeActivity t ON g.participant_type = t.id WHERE g.participant_type IS NOT NULL" . str_replace(array("s.firstname", "s.province", "s.amphur"), array("''", "''", "''"), $where_clause) . " GROUP BY g.participant_type, t.title";
    }
    $stmt = $pdo->prepare($sql);
    $stmt->execute($filter_params);
    $participantTypes = $stmt->fetchAll(PDO::FETCH_ASSOC);
    if (!$participantTypes) {
        $participantTypes = array();
    }

    // Chart 1: รายกลุ่ม (นับครั้ง)
    $sql_group = "SELECT g.guidance_type, COUNT(g.id) as tot, SUM(CASE WHEN g.participant_type = '1' THEN 1 ELSE 0 END) as pt1, SUM(CASE WHEN g.participant_type = '2' THEN 1 ELSE 0 END) as pt2, SUM(CASE WHEN g.participant_type = '3' THEN 1 ELSE 0 END) as pt3, SUM(CASE WHEN g.participant_type = '4' THEN 1 ELSE 0 END) as pt4 FROM group_guidance g LEFT JOIN school s ON g.schoolID = s.schoolID WHERE g.guidance_type IN ('1','2','3') " . $where_clause . " GROUP BY g.guidance_type";
    $stmt = $pdo->prepare($sql_group);
    $stmt->execute($filter_params);
    $groupOverall = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $go_data = array(1 => ['tot' => 0, 'pt1' => 0, 'pt2' => 0, 'pt3' => 0, 'pt4' => 0], 2 => ['tot' => 0, 'pt1' => 0, 'pt2' => 0, 'pt3' => 0, 'pt4' => 0], 3 => ['tot' => 0, 'pt1' => 0, 'pt2' => 0, 'pt3' => 0, 'pt4' => 0]);
    foreach ($groupOverall as $r) {
        $gtype = $r['guidance_type'];
        if (isset($go_data[$gtype])) {
            $go_data[$gtype] = ['tot' => intval($r['tot']), 'pt1' => intval($r['pt1']), 'pt2' => intval($r['pt2']), 'pt3' => intval($r['pt3']), 'pt4' => intval($r['pt4'])];
        }
    }

    // Chart 2: รายคน (นับคน)
    $sql_indiv = "SELECT g.guidance_type, SUM(g.participant_count) as tot, SUM(CASE WHEN g.participant_type = '1' THEN g.participant_count ELSE 0 END) as pt1, SUM(CASE WHEN g.participant_type = '2' THEN g.participant_count ELSE 0 END) as pt2, SUM(CASE WHEN g.participant_type = '3' THEN g.participant_count ELSE 0 END) as pt3, SUM(CASE WHEN g.participant_type = '4' THEN g.participant_count ELSE 0 END) as pt4 FROM group_guidance g LEFT JOIN school s ON g.schoolID = s.schoolID WHERE g.guidance_type IN ('1','2','3') " . $where_clause . " GROUP BY g.guidance_type";
    $stmt = $pdo->prepare($sql_indiv);
    $stmt->execute($filter_params);
    $detailOverall = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $do_data = array(1 => ['tot' => 0, 'pt1' => 0, 'pt2' => 0, 'pt3' => 0, 'pt4' => 0], 2 => ['tot' => 0, 'pt1' => 0, 'pt2' => 0, 'pt3' => 0, 'pt4' => 0], 3 => ['tot' => 0, 'pt1' => 0, 'pt2' => 0, 'pt3' => 0, 'pt4' => 0]);
    foreach ($detailOverall as $r) {
        $gtype = $r['guidance_type'];
        if (isset($do_data[$gtype])) {
            $do_data[$gtype] = ['tot' => intval($r['tot']), 'pt1' => intval($r['pt1']), 'pt2' => intval($r['pt2']), 'pt3' => intval($r['pt3']), 'pt4' => intval($r['pt4'])];
        }
    }

    // 🌟 อัตราความสำเร็จ (จัดเรียงและนับยอดด้วย success_count แทน rate) 🌟
    $sql = "SELECT CASE WHEN g.guidance_type = '1' THEN 'ด้านการศึกษา' WHEN g.guidance_type = '2' THEN 'ด้านอาชีพ' WHEN g.guidance_type = '3' THEN 'ด้านส่วนตัวและสังคม' ELSE 'อื่นๆ' END as guidance_name, SUM(CASE WHEN g.goal_achieved = '1' THEN g.participant_count ELSE 0 END) as success_count, SUM(g.participant_count) as total_count FROM group_guidance g INNER JOIN (SELECT schoolID, guidance_type, MAX(id) as max_id FROM group_guidance GROUP BY schoolID, guidance_type) latest ON g.id = latest.max_id LEFT JOIN school s ON g.schoolID = s.schoolID WHERE g.participant_count IS NOT NULL AND g.guidance_type IS NOT NULL" . $where_clause . " GROUP BY g.guidance_type ORDER BY success_count DESC";
    $stmt = $pdo->prepare($sql);
    $stmt->execute($filter_params);
    $successRates = $stmt->fetchAll(PDO::FETCH_ASSOC);
    if (!$successRates) {
        $successRates = array();
    }

    // TOP 10
    if ($has_school_table) {
        $sql = "SELECT IFNULL({$group_col}, 'ไม่ระบุ') as item_name, COUNT(*) as total_count FROM group_guidance g LEFT JOIN school s ON g.schoolID = s.schoolID WHERE g.schoolID IS NOT NULL" . $where_clause . " GROUP BY item_name HAVING total_count > 0 ORDER BY total_count DESC LIMIT 10";
    } else {
        $sql = "SELECT 'กรุงเทพมหานคร' as item_name, COUNT(*) as total_count FROM group_guidance g WHERE 1=1" . str_replace(array("s.firstname", "s.province", "s.amphur"), array("''", "'กรุงเทพมหานคร'", "''"), $where_clause) . " HAVING total_count > 0 LIMIT 10";
    }
    $stmt = $pdo->prepare($sql);
    $stmt->execute($filter_params);
    $topItems = $stmt->fetchAll(PDO::FETCH_ASSOC);
    if (!$topItems) {
        $topItems = array();
    }

    $dynamicGuidance = array();
    $dynamicDetail = array();
    $dynamicSuccess = array();
    foreach ($topItems as $item) {
        $iname = $item['item_name'];
        $item_params = array($iname);
        if (!empty($filter_params)) {
            foreach ($filter_params as $param) {
                $item_params[] = $param;
            }
        }

        if ($has_school_table) {
            // Dynamic Chart 1
            $stmt = $pdo->prepare("SELECT g.guidance_type, COUNT(g.id) as tot, SUM(CASE WHEN g.participant_type = '1' THEN 1 ELSE 0 END) as pt1, SUM(CASE WHEN g.participant_type = '2' THEN 1 ELSE 0 END) as pt2, SUM(CASE WHEN g.participant_type = '3' THEN 1 ELSE 0 END) as pt3, SUM(CASE WHEN g.participant_type = '4' THEN 1 ELSE 0 END) as pt4 FROM group_guidance g LEFT JOIN school s ON g.schoolID = s.schoolID WHERE IFNULL({$group_col}, 'ไม่ระบุ') = ? AND g.guidance_type IN ('1','2','3') " . $where_clause . " GROUP BY g.guidance_type");
            $stmt->execute($item_params);
            $g_res = $stmt->fetchAll(PDO::FETCH_ASSOC);
            $d_go = array(1 => ['tot' => 0, 'pt1' => 0, 'pt2' => 0, 'pt3' => 0, 'pt4' => 0], 2 => ['tot' => 0, 'pt1' => 0, 'pt2' => 0, 'pt3' => 0, 'pt4' => 0], 3 => ['tot' => 0, 'pt1' => 0, 'pt2' => 0, 'pt3' => 0, 'pt4' => 0]);
            foreach ($g_res as $r) {
                $gtype = $r['guidance_type'];
                if (isset($d_go[$gtype])) {
                    $d_go[$gtype] = ['tot' => intval($r['tot']), 'pt1' => intval($r['pt1']), 'pt2' => intval($r['pt2']), 'pt3' => intval($r['pt3']), 'pt4' => intval($r['pt4'])];
                }
            }
            $dynamicGuidance[$iname] = array(
                'labels' => ['ด้านการศึกษา', 'ด้านอาชีพ', 'ด้านส่วนตัวและสังคม'],
                'data' => [$d_go[1]['tot'], $d_go[2]['tot'], $d_go[3]['tot']],
                'pt1' => [$d_go[1]['pt1'], $d_go[2]['pt1'], $d_go[3]['pt1']],
                'pt2' => [$d_go[1]['pt2'], $d_go[2]['pt2'], $d_go[3]['pt2']],
                'pt3' => [$d_go[1]['pt3'], $d_go[2]['pt3'], $d_go[3]['pt3']],
                'pt4' => [$d_go[1]['pt4'], $d_go[2]['pt4'], $d_go[3]['pt4']]
            );

            // Dynamic Chart 2
            $stmt = $pdo->prepare("SELECT g.guidance_type, SUM(g.participant_count) as tot, SUM(CASE WHEN g.participant_type = '1' THEN g.participant_count ELSE 0 END) as pt1, SUM(CASE WHEN g.participant_type = '2' THEN g.participant_count ELSE 0 END) as pt2, SUM(CASE WHEN g.participant_type = '3' THEN g.participant_count ELSE 0 END) as pt3, SUM(CASE WHEN g.participant_type = '4' THEN g.participant_count ELSE 0 END) as pt4 FROM group_guidance g LEFT JOIN school s ON g.schoolID = s.schoolID WHERE IFNULL({$group_col}, 'ไม่ระบุ') = ? AND g.guidance_type IN ('1','2','3') " . $where_clause . " GROUP BY g.guidance_type");
            $stmt->execute($item_params);
            $i_res = $stmt->fetchAll(PDO::FETCH_ASSOC);
            $d_do = array(1 => ['tot' => 0, 'pt1' => 0, 'pt2' => 0, 'pt3' => 0, 'pt4' => 0], 2 => ['tot' => 0, 'pt1' => 0, 'pt2' => 0, 'pt3' => 0, 'pt4' => 0], 3 => ['tot' => 0, 'pt1' => 0, 'pt2' => 0, 'pt3' => 0, 'pt4' => 0]);
            foreach ($i_res as $r) {
                $gtype = $r['guidance_type'];
                if (isset($d_do[$gtype])) {
                    $d_do[$gtype] = ['tot' => intval($r['tot']), 'pt1' => intval($r['pt1']), 'pt2' => intval($r['pt2']), 'pt3' => intval($r['pt3']), 'pt4' => intval($r['pt4'])];
                }
            }
            $dynamicDetail[$iname] = array(
                'labels' => ['ด้านการศึกษา', 'ด้านอาชีพ', 'ด้านส่วนตัวและสังคม'],
                'data' => [$d_do[1]['tot'], $d_do[2]['tot'], $d_do[3]['tot']],
                'pt1' => [$d_do[1]['pt1'], $d_do[2]['pt1'], $d_do[3]['pt1']],
                'pt2' => [$d_do[1]['pt2'], $d_do[2]['pt2'], $d_do[3]['pt2']],
                'pt3' => [$d_do[1]['pt3'], $d_do[2]['pt3'], $d_do[3]['pt3']],
                'pt4' => [$d_do[1]['pt4'], $d_do[2]['pt4'], $d_do[3]['pt4']]
            );

            // 🌟 Dynamic Chart 3 (ดึงยอดคน success_count โดยตรง) 🌟
            $stmt = $pdo->prepare("SELECT CASE WHEN g.guidance_type = '1' THEN 'ด้านการศึกษา' WHEN g.guidance_type = '2' THEN 'ด้านอาชีพ' WHEN g.guidance_type = '3' THEN 'ด้านส่วนตัวและสังคม' ELSE 'อื่นๆ' END as guidance_name, SUM(CASE WHEN g.goal_achieved = '1' THEN g.participant_count ELSE 0 END) as success_count, SUM(g.participant_count) as total_count FROM group_guidance g INNER JOIN (SELECT schoolID, guidance_type, MAX(id) as max_id FROM group_guidance GROUP BY schoolID, guidance_type) latest ON g.id = latest.max_id LEFT JOIN school s ON g.schoolID = s.schoolID WHERE IFNULL({$group_col}, 'ไม่ระบุ') = ? AND g.participant_count IS NOT NULL AND g.guidance_type IS NOT NULL" . $where_clause . " GROUP BY g.guidance_type ORDER BY success_count DESC");
            $stmt->execute($item_params);
            $s_res = $stmt->fetchAll(PDO::FETCH_ASSOC);
            $s_labels = array();
            $s_success = array();
            $s_total = array();
            foreach ($s_res as $r) {
                $s_labels[] = $r['guidance_name'];
                $s_success[] = intval($r['success_count']);
                $s_total[] = intval($r['total_count']);
            }
            $dynamicSuccess[$iname] = array(
                'labels' => $s_labels ? $s_labels : array('ไม่มีข้อมูล'),
                'data' => $s_success ? $s_success : array(0), // ส่งยอด success เข้าไปวาดกราฟแท่ง
                'success_count' => $s_success ? $s_success : array(0),
                'total_count' => $s_total ? $s_total : array(0)
            );
        } else {
            $dynamicGuidance[$iname] = array('labels' => array('ไม่มีข้อมูล'), 'data' => array(0), 'pt1' => array(0), 'pt2' => array(0), 'pt3' => array(0), 'pt4' => array(0));
            $dynamicDetail[$iname] = array('labels' => array('ไม่มีข้อมูล'), 'data' => array(0), 'pt1' => array(0), 'pt2' => array(0), 'pt3' => array(0), 'pt4' => array(0));
            $dynamicSuccess[$iname] = array('labels' => array('ไม่มีข้อมูล'), 'data' => array(0), 'success_count' => array(0), 'total_count' => array(0));
        }
    }
} catch (PDOException $e) {
    $db_error_message = $e->getMessage();
}
?>

<!DOCTYPE html>
<html lang="th">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Dashboard แบบรายกลุ่ม</title>

    <style>
        * {
            margin: 0;
            padding: 0;
            box-sizing: border-box;
        }

        body {
            font-family: 'Inter', 'Sarabun', sans-serif;
            background: #f8fafc;
            min-height: 100vh;
            padding: 25px;
            color: #334155;
        }

        .dashboard {
            max-width: 1600px;
            margin: 0 auto;
            display: flex;
            flex-direction: column;
            gap: 24px;
            padding-top: 5px;
        }

        #sidebar.active,
        #sidebar.show {
            transform: translateX(0) !important;
            left: 0 !important;
        }

        #sidebarOverlay.active,
        #sidebarOverlay.show {
            display: block !important;
            opacity: 1 !important;
            z-index: 998;
        }

        .top-header {
            background: #ffffff;
            border-radius: 16px;
            padding: 24px 30px;
            box-shadow: 0 4px 6px -1px rgba(0, 0, 0, 0.05);
            border: 1px solid #e2e8f0;
        }

        .header-main-row {
            display: flex;
            justify-content: space-between;
            align-items: center;
            flex-wrap: wrap;
            gap: 15px;
            border-bottom: 1px solid #f1f5f9;
            padding-bottom: 15px;
            margin-bottom: 15px;
        }

        .header-titles {
            display: flex;
            flex-direction: column;
            gap: 4px;
        }

        .dashboard-title {
            color: #0f172a;
            font-size: 1.8rem;
            font-weight: 800;
            margin: 0;
            line-height: 1.2;
        }

        .role-badge {
            display: inline-block;
            padding: 4px 12px;
            background: #e0e7ff;
            color: #4338ca;
            border-radius: 20px;
            font-size: 0.85rem;
            font-weight: 700;
            width: fit-content;
        }

        .toggle-buttons {
            display: flex;
            gap: 10px;
            flex-wrap: wrap;
        }

        .toggle-btn {
            padding: 8px 18px;
            border: 1px solid #cbd5e1;
            background: #f8fafc;
            color: #64748b;
            border-radius: 8px;
            font-size: 0.95rem;
            font-weight: 600;
            cursor: pointer;
            text-decoration: none;
            transition: all 0.2s;
            white-space: nowrap;
        }

        .toggle-btn.active {
            background: #1e40af;
            color: white;
            border-color: #1e40af;
        }

        .filter-wrapper {
            width: 100%;
            margin-top: 10px;
        }

        .compact-filter {
            display: flex;
            flex-wrap: wrap;
            gap: 15px;
            align-items: flex-end;
            justify-content: space-between;
            width: 100%;
        }

        .filter-inputs {
            display: flex;
            flex-wrap: wrap;
            gap: 15px;
            flex: 1;
        }

        .filter-group {
            flex: 1 1 200px;
            max-width: 280px;
            display: flex;
            flex-direction: column;
            gap: 6px;
        }

        .filter-group label {
            font-size: 0.85rem;
            font-weight: 600;
            color: #64748b;
            margin: 0;
        }

        .filter-group input,
        .filter-group select {
            width: 100%;
            height: 42px;
            padding: 8px 14px;
            border: 1px solid #cbd5e1 !important;
            border-radius: 8px;
            font-size: 0.95rem;
            color: #334155;
            background-color: #fff !important;
            outline: none;
            -webkit-appearance: none;
            appearance: none;
        }

        .filter-actions {
            display: flex;
            gap: 10px;
            flex-wrap: wrap;
            justify-content: flex-end;
            margin-top: 5px;
        }

        .btn-small,
        .btn-excel {
            height: 42px;
            padding: 0 16px;
            border: none;
            border-radius: 8px;
            font-size: 0.95rem;
            font-weight: 600;
            cursor: pointer;
            color: white;
            display: flex;
            align-items: center;
            justify-content: center;
            gap: 6px;
        }

        .search-btn {
            background: #2563eb;
        }

        .reset-btn {
            background: #64748b;
        }

        .btn-excel {
            background-color: #10b981;
            max-width: fit-content;
            text-decoration: none;
        }

        @media (max-width: 1024px) {
            .filter-group {
                max-width: none;
            }

            .filter-actions {
                flex: 1 1 100%;
                justify-content: flex-end;
            }
        }

        @media (max-width: 768px) {
            .header-main-row {
                flex-direction: column;
                align-items: flex-start;
            }

            .toggle-buttons {
                width: 100%;
            }

            .toggle-btn {
                flex: 1;
                text-align: center;
            }

            .filter-inputs {
                flex-direction: column;
                gap: 10px;
            }

            .filter-group {
                width: 100%;
                flex: none;
                max-width: none;
            }

            .filter-actions {
                justify-content: space-between;
            }

            .filter-actions button {
                flex: 1;
            }
        }

        .stats-row {
            display: grid;
            grid-template-columns: repeat(auto-fit, minmax(200px, 1fr));
            gap: 20px;
        }

        .stat-card {
            background: #ffffff;
            border-radius: 12px;
            padding: 24px 15px;
            text-align: center;
            border: 1px solid #e2e8f0;
            box-shadow: 0 4px 6px -1px rgba(0, 0, 0, 0.05);
            display: flex;
            flex-direction: column;
            justify-content: center;
            align-items: center;
            min-height: 120px;
        }

        .stat-card.main {
            background: linear-gradient(135deg, #1e40af 0%, #3b82f6 100%);
            border: none;
        }

        .stat-card.main .stat-number {
            color: #ffffff;
        }

        .stat-card.main .stat-label {
            color: #e2e8f0;
        }

        .stat-card.lifelong {
            border-top: 5px solid #059669;
        }

        .stat-card.lifelong .stat-number {
            color: #059669;
        }

        .stat-card.development {
            border-top: 5px solid #d97706;
        }

        .stat-card.development .stat-number {
            color: #d97706;
        }

        .stat-card.qualification {
            border-top: 5px solid #7c3aed;
        }

        .stat-card.qualification .stat-number {
            color: #7c3aed;
        }

        .stat-card.public {
            border-top: 5px solid #475569;
        }

        .stat-card.public .stat-number {
            color: #475569;
        }

        .stat-number {
            font-size: 2.2rem;
            font-weight: 800;
            line-height: 1;
            margin-bottom: 12px;
        }

        .stat-label {
            font-size: 0.9rem;
            font-weight: 600;
            color: #475569;
            line-height: 1.5;
        }

        .chart-box {
            background: #ffffff;
            border-radius: 16px;
            padding: 24px;
            box-shadow: 0 4px 6px -1px rgba(0, 0, 0, 0.05);
            border: 1px solid #e2e8f0;
        }

        .chart-header {
            display: flex;
            justify-content: space-between;
            align-items: center;
            margin-bottom: 20px;
            flex-wrap: wrap;
            gap: 10px;
        }

        .chart-title {
            font-size: 1.15rem;
            color: #1e293b;
            margin-bottom: 20px;
            font-weight: 700;
            text-align: center;
            margin-top: 0;
        }

        .hero-row {
            width: 100%;
        }

        .hero-chart-wrapper {
            position: relative;
            height: 350px;
            width: 100%;
        }

        .bottom-row {
            display: grid;
            grid-template-columns: repeat(auto-fit, minmax(300px, 1fr));
            gap: 24px;
        }

        .small-chart-wrapper {
            position: relative;
            height: 260px;
            width: 100%;
        }
    </style>
</head>

<body>
    <div class="dashboard">

        <?php if (!empty($db_error_message)): ?>
            <div style="background: #f8d7da; color: #721c24; padding: 15px; border-radius: 8px; border: 1px solid #f5c6cb; margin-bottom: 15px;">
                <strong>🚨 พบข้อผิดพลาด (SQL Error):</strong><br><code style="font-size: 1rem;"><?php echo htmlspecialchars($db_error_message); ?></code>
            </div>
        <?php endif; ?>

        <div class="top-header">
            <div class="header-main-row">
                <div class="header-titles">
                    <span class="role-badge">สถานะการใช้งาน: <?php echo $role_name; ?></span>
                    <h1 class="dashboard-title">Dashboard แบบรายกลุ่ม</h1>
                </div>
                <div class="toggle-buttons">
                    <a href="index.php" class="toggle-btn active">👥 แบบรายกลุ่ม</a>
                    <a href="dashboardperson.php" class="toggle-btn">👤 แบบรายบุคคล</a>
                </div>
            </div>

            <div class="filter-wrapper">
                <form id="filterForm" method="POST" action="" class="compact-filter">
                    <div class="filter-inputs">
                        <div class="filter-group">
                            <label>วันที่เริ่มต้น</label>
                            <input type="date" id="start_date" name="start_date" value="<?php echo htmlspecialchars($start_date); ?>">
                        </div>
                        <div class="filter-group">
                            <label>ถึงวันที่</label>
                            <input type="date" id="end_date" name="end_date" value="<?php echo htmlspecialchars($end_date); ?>">
                        </div>
                        <div class="filter-group">
                            <label>ประเภทการแนะแนว</label>
                            <select id="service_type" name="service_type">
                                <option value="">ทั้งหมด</option>
                                <option value="1" <?php echo $service_type == '1' ? 'selected' : ''; ?>>ด้านการศึกษา</option>
                                <option value="2" <?php echo $service_type == '2' ? 'selected' : ''; ?>>ด้านอาชีพ</option>
                                <option value="3" <?php echo $service_type == '3' ? 'selected' : ''; ?>>ด้านส่วนตัวและสังคม</option>
                            </select>
                        </div>

                        <?php if ($show_search_box): ?>
                            <div class="filter-group">
                                <label><?php echo $search_box_label; ?></label>
                                <input type="text" id="search_term" name="search_term" placeholder="<?php echo $search_box_label; ?>..." value="<?php echo htmlspecialchars($search_term); ?>">
                            </div>
                        <?php endif; ?>
                    </div>

                    <div class="filter-actions">
                        <button type="submit" class="btn-small search-btn"><?php echo $show_search_box ? 'ค้นหา' : 'กรองข้อมูล'; ?></button>
                        <button type="button" class="btn-small reset-btn" onclick="resetFilters()">รีเซ็ต</button>
                        <button type="button" onclick="exportToExcel()" class="btn-small btn-excel">📊 Export Excel</button>
                    </div>
                </form>
            </div>
        </div>

        <div class="stats-row">
            <div class="stat-card" style="background: linear-gradient(135deg, #0f766e 0%, #14b8a6 100%); border: none;">
                <div class="stat-number" style="color: #ffffff;"><?php echo number_format($totalEvents); ?></div>
                <div class="stat-label" style="color: #e2e8f0;">จำนวนครั้งที่จัด<br>กิจกรรมทั้งหมด</div>
            </div>
            <div class="stat-card main">
                <div class="stat-number" id="totalParticipants"><?php echo number_format($totalParticipants); ?></div>
                <div class="stat-label">จำนวนผู้เข้าร่วม<br>กิจกรรมทั้งหมด</div>
            </div>

            <?php
            $classes = array('lifelong', 'development', 'qualification', 'public', 'main');
            $participantTypesArray = array();
            $idx = 0;

            if ($ta_rows) {
                foreach ($ta_rows as $row) {
                    $c = isset($classes[$idx]) ? $classes[$idx] : 'public';
                    $label = !empty($row['title']) ? $row['title'] : 'ไม่ระบุ';
                    $participantTypesArray[$label] = array('class' => $c, 'count' => 0, 'label' => $label);
                    $idx++;
                }
            } else {
                $participantTypesArray = array(
                    'การเรียนรู้ตลอดชีวิต' => array('class' => 'lifelong', 'count' => 0, 'label' => 'การเรียนรู้ตลอดชีวิต'),
                    'การเรียนรู้เพื่อการพัฒนาตนเอง' => array('class' => 'development', 'count' => 0, 'label' => 'การเรียนรู้เพื่อการพัฒนาตนเอง'),
                    'การเรียนรู้เพื่อคุณวุฒิตามระดับ' => array('class' => 'qualification', 'count' => 0, 'label' => 'การเรียนรู้เพื่อคุณวุฒิตามระดับ'),
                    'ประชาชน' => array('class' => 'public', 'count' => 0, 'label' => 'ประชาชน (ทั่วไป)')
                );
            }

            foreach ($participantTypes as $type) {
                $tname = !empty($type['type_name']) ? $type['type_name'] : 'ไม่ระบุ';
                if (isset($participantTypesArray[$tname])) {
                    $participantTypesArray[$tname]['count'] = intval($type['count']);
                } else {
                    $participantTypesArray[$tname] = array('class' => 'public', 'count' => intval($type['count']), 'label' => $tname);
                }
            }

            foreach ($participantTypesArray as $data): ?>
                <div class="stat-card <?php echo $data['class']; ?>">
                    <div class="stat-number"><?php echo number_format($data['count']); ?></div>
                    <div class="stat-label"><?php echo $data['label']; ?></div>
                </div>
            <?php endforeach; ?>
        </div>

        <?php if ($current_user_type != 4): ?>
            <div class="hero-row chart-box">
                <div class="chart-header">
                    <div class="chart-title" style="margin-bottom:0;">TOP 10 <?php echo $chart_label; ?> ที่ให้บริการแนะแนวมากที่สุด (คลิกที่แท่งเพื่อดูรายละเอียด)</div>
                </div>
                <div class="hero-chart-wrapper"><canvas id="itemChart"></canvas></div>
            </div>
        <?php endif; ?>

        <div class="bottom-row">
            <div class="chart-box">
                <div class="chart-title" id="guidanceTitle">ประเภทการแนะแนว (จำนวนครั้ง) แบ่งเป็นรายด้าน</div>
                <div class="small-chart-wrapper"><canvas id="guidanceTypeChart"></canvas></div>
            </div>
            <div class="chart-box">
                <div class="chart-title" id="detailTitle">ประเภทการแนะแนว (จำนวนผู้เข้าร่วมกิจกรรม) แบ่งเป็นรายด้าน</div>
                <div class="small-chart-wrapper"><canvas id="detailChart"></canvas></div>
            </div>
            <div class="chart-box">
                <div class="chart-title" id="successTitle">ความสำเร็จ (จำนวนคน) - ภาพรวม</div>
                <div class="small-chart-wrapper"><canvas id="successRateChart"></canvas></div>
            </div>
        </div>
    </div>

    <script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@4.5.2/dist/js/bootstrap.bundle.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/chartjs-plugin-datalabels@2.2.0/dist/chartjs-plugin-datalabels.min.js"></script>

    <script>
        document.addEventListener("DOMContentLoaded", function() {
            const toggleBtn = document.getElementById('sidebarToggle');
            const sidebar = document.getElementById('sidebar');
            const overlay = document.getElementById('sidebarOverlay');

            if (toggleBtn && sidebar) {
                toggleBtn.addEventListener('click', function(e) {
                    e.preventDefault();
                    sidebar.classList.toggle('active');
                    sidebar.classList.toggle('show');
                    if (overlay) {
                        overlay.classList.toggle('active');
                        overlay.classList.toggle('show');
                    }
                });
            }
            if (overlay) {
                overlay.addEventListener('click', function() {
                    sidebar.classList.remove('active');
                    sidebar.classList.remove('show');
                    overlay.classList.remove('active');
                    overlay.classList.remove('show');
                });
            }
        });

        if (typeof Chart === 'undefined') {
            var wrappers = document.querySelectorAll('.hero-chart-wrapper, .small-chart-wrapper');
            for (var i = 0; i < wrappers.length; i++) {
                wrappers[i].innerHTML = '<div style="color: #6b7280; padding: 40px; text-align: center; border: 1px dashed #d1d5db;">📊 กราฟไม่สามารถแสดงได้</div>';
            }
        } else {
            Chart.defaults.color = '#64748b';
            Chart.defaults.font.family = "'Inter', 'Sarabun', sans-serif";
            const chartLabelType = "<?php echo $chart_label; ?>";

            // 🌟 เปิดใช้งานและตั้งค่าตัวเลขบนกราฟ 🌟
            Chart.register(ChartDataLabels);
            Chart.defaults.set('plugins.datalabels', {
                color: '#ffffff', // สีตัวเลข (สีขาว)
                font: {
                    weight: 'bold',
                    size: 14,
                    family: "'Inter', 'Sarabun', sans-serif"
                },
                formatter: function(value, context) {
                    return value > 0 ? value : ''; // ถ้าเป็น 0 ให้ซ่อนตัวเลขไว้จะได้ไม่รก
                }
            });

            const dynamicGuidance = <?php echo safe_json($dynamicGuidance, '{}'); ?>;
            const dynamicDetail = <?php echo safe_json($dynamicDetail, '{}'); ?>;
            const dynamicSuccess = <?php echo safe_json($dynamicSuccess, '{}'); ?>;

            let itemChart, guidanceChart, detailChart, successChart;

            <?php if ($current_user_type != 4): ?>
                const itemData = {
                    labels: <?php
                            $iLabels = array();
                            foreach ($topItems as $p) {
                                $iLabels[] = $p['item_name'];
                            }
                            echo safe_json($iLabels ? $iLabels : array('ไม่มีข้อมูล'), '["ไม่มีข้อมูล"]');
                            ?>,
                    datasets: [{
                        label: 'จำนวนครั้งที่จัดกิจกรรม',
                        data: <?php
                                $iCounts = array();
                                foreach ($topItems as $p) {
                                    $iCounts[] = intval($p['total_count']);
                                }
                                echo safe_json($iCounts ? $iCounts : array(0), '[0]');
                                ?>,
                        backgroundColor: '#3b82f6',
                        borderRadius: 4
                    }]
                };
                const itemCtx = document.getElementById('itemChart');
                if (itemCtx) {
                    itemChart = new Chart(itemCtx.getContext('2d'), {
                        type: 'bar',
                        data: itemData,
                        options: {
                            responsive: true,
                            maintainAspectRatio: false,
                            scales: {
                                y: {
                                    beginAtZero: true,
                                    grid: {
                                        color: '#f1f5f9'
                                    }
                                },
                                x: {
                                    grid: {
                                        display: false
                                    }
                                }
                            },
                            plugins: {
                                legend: {
                                    display: false
                                },
                                datalabels: {
                                    font: {
                                        size: 40, // 🌟 ปรับขนาดตัวเลขตรงนี้ (ค่าเดิมคือ 14)
                                        weight: 'bold',
                                        family: "'Inter', 'Sarabun', sans-serif"
                                    }
                                }
                            },
                            onClick: (e, elements) => {
                                if (elements.length > 0) {
                                    const index = elements[0].index;
                                    updateChartsData(itemData.labels[index]);
                                }
                            }
                        }
                    });
                }
            <?php endif; ?>

            const doughnutBreakdownTooltipGroup = {
                callbacks: {
                    label: function(context) {
                        let value = context.raw || 0;
                        // ใส่คำว่า "จำแนกเป็น" เฉพาะในนี้
                        return ` ${context.label} ${value} ครั้ง     จำแนกเป็น`;
                    },
                    afterBody: function(context) {
                        const ds = context[0].dataset;
                        const idx = context[0].dataIndex;
                        let lines = [];

                        const titles = {
                            pt1: '<?php echo isset($ta_titles[1]) ? $ta_titles[1] : "ตลอดชีวิต"; ?>',
                            pt2: '<?php echo isset($ta_titles[2]) ? $ta_titles[2] : "พัฒนาตนเอง"; ?>',
                            pt3: '<?php echo isset($ta_titles[3]) ? $ta_titles[3] : "คุณวุฒิฯ"; ?>',
                            pt4: '<?php echo isset($ta_titles[4]) ? $ta_titles[4] : "ประชาชน"; ?>'
                        };

                        if (ds.pt1) lines.push(`  - ${titles.pt1}: ${ds.pt1[idx]} ครั้ง`);
                        if (ds.pt2) lines.push(`  - ${titles.pt2}: ${ds.pt2[idx]} ครั้ง`);
                        if (ds.pt3) lines.push(`  - ${titles.pt3}: ${ds.pt3[idx]} ครั้ง`);
                        if (ds.pt4) lines.push(`  - ${titles.pt4}: ${ds.pt4[idx]} ครั้ง`);

                        return lines;
                    }
                }
            };

            const doughnutBreakdownTooltipPerson = {
                callbacks: {
                    // 2. แสดงชื่อด้าน + จำแนกเป็น (เฉพาะจุดนี้)
                    label: function(context) {
                        let value = context.raw || 0;
                        // เพิ่มคำว่า "จำแนกเป็น" เข้าไปในบรรทัดที่มีสี่เหลี่ยมสี
                        return ` ${context.label} ${value} คน     จำแนกเป็น`;
                    },
                    // 3. รายการย่อยด้านล่าง
                    afterBody: function(context) {
                        const ds = context[0].dataset;
                        const idx = context[0].dataIndex;
                        let lines = [];

                        // ดึงชื่อประเภทจาก PHP ตามโค้ดต้นฉบับของคุณ
                        const titles = {
                            pt1: '<?php echo isset($ta_titles[1]) ? $ta_titles[1] : "ตลอดชีวิต"; ?>',
                            pt2: '<?php echo isset($ta_titles[2]) ? $ta_titles[2] : "พัฒนาตนเอง"; ?>',
                            pt3: '<?php echo isset($ta_titles[3]) ? $ta_titles[3] : "คุณวุฒิฯ"; ?>',
                            pt4: '<?php echo isset($ta_titles[4]) ? $ta_titles[4] : "ประชาชน"; ?>'
                        };

                        // ตรวจสอบค่าและเพิ่มรายการย่อย
                        if (ds.pt1) lines.push(`  - ${titles.pt1}: ${ds.pt1[idx]} คน`);
                        if (ds.pt2) lines.push(`  - ${titles.pt2}: ${ds.pt2[idx]} คน`);
                        if (ds.pt3) lines.push(`  - ${titles.pt3}: ${ds.pt3[idx]} คน`);
                        if (ds.pt4) lines.push(`  - ${titles.pt4}: ${ds.pt4[idx]} คน`);

                        return lines;
                    }
                }
            };

            const guidanceCtx = document.getElementById('guidanceTypeChart');
            if (guidanceCtx) {
                guidanceChart = new Chart(guidanceCtx.getContext('2d'), {
                    type: 'doughnut',
                    data: {
                        labels: ['ด้านการศึกษา', 'ด้านอาชีพ', 'ด้านส่วนตัวและสังคม'],
                        datasets: [{
                            data: [<?php echo isset($go_data[1]['tot']) ? $go_data[1]['tot'] : 0; ?>, <?php echo isset($go_data[2]['tot']) ? $go_data[2]['tot'] : 0; ?>, <?php echo isset($go_data[3]['tot']) ? $go_data[3]['tot'] : 0; ?>],
                            pt1: [<?php echo isset($go_data[1]['pt1']) ? $go_data[1]['pt1'] : 0; ?>, <?php echo isset($go_data[2]['pt1']) ? $go_data[2]['pt1'] : 0; ?>, <?php echo isset($go_data[3]['pt1']) ? $go_data[3]['pt1'] : 0; ?>],
                            pt2: [<?php echo isset($go_data[1]['pt2']) ? $go_data[1]['pt2'] : 0; ?>, <?php echo isset($go_data[2]['pt2']) ? $go_data[2]['pt2'] : 0; ?>, <?php echo isset($go_data[3]['pt2']) ? $go_data[3]['pt2'] : 0; ?>],
                            pt3: [<?php echo isset($go_data[1]['pt3']) ? $go_data[1]['pt3'] : 0; ?>, <?php echo isset($go_data[2]['pt3']) ? $go_data[2]['pt3'] : 0; ?>, <?php echo isset($go_data[3]['pt3']) ? $go_data[3]['pt3'] : 0; ?>],
                            pt4: [<?php echo isset($go_data[1]['pt4']) ? $go_data[1]['pt4'] : 0; ?>, <?php echo isset($go_data[2]['pt4']) ? $go_data[2]['pt4'] : 0; ?>, <?php echo isset($go_data[3]['pt4']) ? $go_data[3]['pt4'] : 0; ?>],
                            backgroundColor: ['#10b981', '#3b82f6', '#f59e0b'],
                            borderWidth: 2,
                            borderColor: '#ffffff'
                        }]
                    },
                    options: {
                        responsive: true,
                        maintainAspectRatio: false,
                        cutout: '65%',
                        plugins: {
                            legend: {
                                position: 'bottom'
                            },
                            tooltip: doughnutBreakdownTooltipGroup
                        }
                    }
                });
            }

            const detailCtx = document.getElementById('detailChart');
            if (detailCtx) {
                detailChart = new Chart(detailCtx.getContext('2d'), {
                    type: 'doughnut',
                    data: {
                        labels: ['ด้านการศึกษา', 'ด้านอาชีพ', 'ด้านส่วนตัวและสังคม'],
                        datasets: [{
                            data: [<?php echo isset($do_data[1]['tot']) ? $do_data[1]['tot'] : 0; ?>, <?php echo isset($do_data[2]['tot']) ? $do_data[2]['tot'] : 0; ?>, <?php echo isset($do_data[3]['tot']) ? $do_data[3]['tot'] : 0; ?>],
                            pt1: [<?php echo isset($do_data[1]['pt1']) ? $do_data[1]['pt1'] : 0; ?>, <?php echo isset($do_data[2]['pt1']) ? $do_data[2]['pt1'] : 0; ?>, <?php echo isset($do_data[3]['pt1']) ? $do_data[3]['pt1'] : 0; ?>],
                            pt2: [<?php echo isset($do_data[1]['pt2']) ? $do_data[1]['pt2'] : 0; ?>, <?php echo isset($do_data[2]['pt2']) ? $do_data[2]['pt2'] : 0; ?>, <?php echo isset($do_data[3]['pt2']) ? $do_data[3]['pt2'] : 0; ?>],
                            pt3: [<?php echo isset($do_data[1]['pt3']) ? $do_data[1]['pt3'] : 0; ?>, <?php echo isset($do_data[2]['pt3']) ? $do_data[2]['pt3'] : 0; ?>, <?php echo isset($do_data[3]['pt3']) ? $do_data[3]['pt3'] : 0; ?>],
                            pt4: [<?php echo isset($do_data[1]['pt4']) ? $do_data[1]['pt4'] : 0; ?>, <?php echo isset($do_data[2]['pt4']) ? $do_data[2]['pt4'] : 0; ?>, <?php echo isset($do_data[3]['pt4']) ? $do_data[3]['pt4'] : 0; ?>],
                            backgroundColor: ['#10b981', '#3b82f6', '#f59e0b'],
                            borderWidth: 2,
                            borderColor: '#ffffff'
                        }]
                    },
                    options: {
                        responsive: true,
                        maintainAspectRatio: false,
                        cutout: '65%',
                        plugins: {
                            legend: {
                                position: 'bottom'
                            },
                            tooltip: doughnutBreakdownTooltipPerson
                        }
                    }
                });
            }

            const successCtx = document.getElementById('successRateChart');
            if (successCtx) {
                successChart = new Chart(successCtx.getContext('2d'), {
                    type: 'bar',
                    data: {
                        labels: <?php
                                $sLabels = array();
                                foreach ($successRates as $r) {
                                    $sLabels[] = $r['guidance_name'];
                                }
                                echo safe_json($sLabels ? $sLabels : array('ไม่มีข้อมูล'), '["ไม่มีข้อมูล"]');
                                ?>,
                        datasets: [{
                            label: 'สำเร็จ (คน)',
                            data: <?php
                                    $sSuccess = array();
                                    foreach ($successRates as $r) {
                                        $sSuccess[] = intval($r['success_count']);
                                    }
                                    echo safe_json($sSuccess ? $sSuccess : array(0), '[0]');
                                    ?>,
                            backgroundColor: ['#10b981', '#3b82f6', '#f59e0b'],
                            borderRadius: 4
                        }]
                    },
                    options: {
                        responsive: true,
                        maintainAspectRatio: false,
                        scales: {
                            y: {
                                beginAtZero: true,
                                ticks: {
                                    stepSize: 1,
                                    precision: 0
                                }
                            },
                            x: {
                                grid: {
                                    display: false
                                }
                            }
                        },
                        plugins: {
                            legend: {
                                display: false
                            },
                            tooltip: {
                                callbacks: {
                                    label: function(context) {
                                        return ` สำเร็จ จำนวน ${context.raw || 0} คน`;
                                    }
                                }
                            }
                        }
                    }
                });
            }

            window.updateChartsData = function(itemName) {
                document.getElementById('guidanceTitle').textContent = `ประเภทการแนะแนวรายกลุ่ม - ${itemName} จำแนกเป็น`;
                document.getElementById('detailTitle').textContent = `ประเภทการแนะแนวรายคน - ${itemName} จำแนกเป็น`;
                document.getElementById('successTitle').textContent = `ความสำเร็จ (จำนวนคน) - ${itemName}`;

                if (guidanceChart && dynamicGuidance[itemName]) {
                    guidanceChart.data.labels = dynamicGuidance[itemName].labels;
                    guidanceChart.data.datasets[0].data = dynamicGuidance[itemName].data;
                    guidanceChart.data.datasets[0].pt1 = dynamicGuidance[itemName].pt1;
                    guidanceChart.data.datasets[0].pt2 = dynamicGuidance[itemName].pt2;
                    guidanceChart.data.datasets[0].pt3 = dynamicGuidance[itemName].pt3;
                    guidanceChart.data.datasets[0].pt4 = dynamicGuidance[itemName].pt4;
                    guidanceChart.update();
                }
                if (detailChart && dynamicDetail[itemName]) {
                    detailChart.data.labels = dynamicDetail[itemName].labels;
                    detailChart.data.datasets[0].data = dynamicDetail[itemName].data;
                    detailChart.data.datasets[0].pt1 = dynamicDetail[itemName].pt1;
                    detailChart.data.datasets[0].pt2 = dynamicDetail[itemName].pt2;
                    detailChart.data.datasets[0].pt3 = dynamicDetail[itemName].pt3;
                    detailChart.data.datasets[0].pt4 = dynamicDetail[itemName].pt4;
                    detailChart.update();
                }
                if (successChart && dynamicSuccess[itemName]) {
                    successChart.data.labels = dynamicSuccess[itemName].labels;
                    successChart.data.datasets[0].data = dynamicSuccess[itemName].data;
                    successChart.update();
                }
            }
        }

        function resetFilters() {
            document.getElementById('start_date').value = '';
            document.getElementById('end_date').value = '';
            document.getElementById('service_type').value = '';
            if (document.getElementById('search_term')) {
                document.getElementById('search_term').value = '';
            }
            document.getElementById('filterForm').submit();
        }

        function exportToExcel() {
            const start_date = document.getElementById('start_date') ? document.getElementById('start_date').value : '';
            const end_date = document.getElementById('end_date') ? document.getElementById('end_date').value : '';
            const service_type = document.getElementById('service_type') ? document.getElementById('service_type').value : '';
            let search_term = '';
            if (document.getElementById('search_term')) {
                search_term = document.getElementById('search_term').value;
            }
            const url = `report/excel-Group.php?start_date=${encodeURIComponent(start_date)}&end_date=${encodeURIComponent(end_date)}&service_type=${encodeURIComponent(service_type)}&search_term=${encodeURIComponent(search_term)}`;
            window.open(url, '_blank');
        }

        var showFollowUpAlert = <?php echo $show_followup_alert ? 'true' : 'false'; ?>;
        $(document).ready(function() {
            if (showFollowUpAlert) {
                checkFollowUpAlerts(false);
            }
        });

        function checkFollowUpAlerts(isManualClick) {
            fetch('get_follow_up_alerts.php')
                .then(response => {
                    if (!response.ok) {
                        throw new Error('404');
                    }
                    return response.json();
                })
                .then(data => {
                    if (data.success) {
                        displayFollowUpAlerts(data.alerts);
                        if (data.count > 0 && !isManualClick && !$('#followUpAlertsModal').hasClass('show')) {
                            $('#followUpAlertsModal').modal('show');
                        }
                    }
                })
                .catch(error => {
                    console.log('ไม่มีไฟล์การแจ้งเตือนในระบบ');
                });
        }

        function displayFollowUpAlerts(alerts) {
            let html = '';
            if (!alerts || alerts.length === 0) {
                html = '<div class="alert alert-info text-center"><i class="fas fa-info-circle me-2"></i>ตอนนี้ไม่มีรายการนัดหมายที่ต้องติดตามผลครับ</div>';
                document.getElementById('followUpAlertsContent').innerHTML = html;
                return;
            }
            html = '<div class="table-responsive"><table class="table table-hover"><thead class="table-warning"><tr><th><i class="fas fa-user me-2"></i>ชื่อผู้รับบริการ</th><th><i class="fas fa-calendar me-2"></i>วันนัดหมาย</th><th><i class="fas fa-cog me-2"></i>จัดการ</th></tr></thead><tbody>';
            alerts.forEach(alert => {
                const followUpDate = new Date(alert.follow_up_date);
                const today = new Date();
                today.setHours(0, 0, 0, 0);
                followUpDate.setHours(0, 0, 0, 0);
                let badgeClass = 'badge-danger';
                let badgeText = 'การนัดหมายติดตาม';
                if (followUpDate.getTime() === today.getTime()) {
                    badgeClass = 'badge-danger';
                    badgeText = 'วันนี้';
                } else if (followUpDate.getTime() - today.getTime() <= 3 * 24 * 60 * 60 * 1000 && followUpDate.getTime() > today.getTime()) {
                    badgeClass = 'badge-warning';
                    badgeText = 'ใกล้มาก';
                } else if (followUpDate.getTime() - today.getTime() <= 7 * 24 * 60 * 60 * 1000 && followUpDate.getTime() > today.getTime()) {
                    badgeClass = 'badge-warning';
                    badgeText = 'อีก ' + Math.ceil((followUpDate.getTime() - today.getTime()) / (24 * 60 * 60 * 1000)) + ' วัน';
                }

                html += `<tr><td><strong>${escapeHtml(alert.first_name + ' ' + alert.last_name)}</strong><br><small class="text-muted">ครั้งที่ ${alert.service_request_number}</small></td><td><span class="badge ${badgeClass}">${escapeHtml(alert.follow_up_date)}</span><br><small class="text-muted">${badgeText}</small></td><td><a href="individual_service_history.php?person_id=${alert.person_id}" class="btn btn-sm btn-primary" title="ดูประวัติการรับบริการ"><i class="fas fa-eye me-1"></i>ดูประวัติ</a></td></tr>`;
            });
            html += '</tbody></table></div>';
            document.getElementById('followUpAlertsContent').innerHTML = html;
        }

        function escapeHtml(text) {
            const map = {
                '&': '&amp;',
                '<': '&lt;',
                '>': '&gt;',
                '"': '&quot;',
                "'": '&#039;'
            };
            return text.replace(/[&<>"']/g, m => map[m]);
        }
    </script>

    <div class="modal fade" id="followUpAlertsModal" tabindex="-1" role="dialog" aria-hidden="true">
        <div class="modal-dialog modal-lg modal-dialog-scrollable" role="document">
            <div class="modal-content" style="border-radius: 12px; border: none; box-shadow: 0 10px 25px rgba(0,0,0,0.2);">
                <div class="modal-header bg-warning text-dark" style="border-radius: 12px 12px 0 0;">
                    <h5 class="modal-title" id="followUpAlertsLabel"><i class="fas fa-bell me-2"></i>แจ้งเตือนการนัดหมายติดตาม</h5>
                    <button type="button" class="close" data-dismiss="modal" aria-label="Close" style="color: black;"><span aria-hidden="true">&times;</span></button>
                </div>
                <div class="modal-body" id="followUpAlertsContent">
                    <div class="text-center py-4">
                        <div class="spinner-border text-warning" role="status"></div>
                        <p class="mt-2">กำลังโหลดข้อมูล...</p>
                    </div>
                </div>
                <div class="modal-footer"><button type="button" class="btn btn-secondary" data-dismiss="modal"><i class="fas fa-times me-2"></i>ปิด</button></div>
            </div>
        </div>
    </div>

</body>

</html>