<?php
session_start();
include 'config.php';
require_once 'rating_helpers.php';

function clean_out($text)
{
    return htmlspecialchars(html_entity_decode($text, ENT_QUOTES | ENT_HTML5), ENT_QUOTES | ENT_HTML5);
}

// A7: Global Filters
$global_filter = "is_verified = 1";
$f_county = $_GET['county'] ?? '';
$f_sector = $_GET['sector'] ?? '';
$f_search = $_GET['q'] ?? '';
$f_subcategory = $_GET['subcategory'] ?? '';
$f_rating = $_GET['rating'] ?? '';
$f_sort = $_GET['sort'] ?? 'most-viewed';
$f_subcategory_name = '';

// Get subcategory name if filtering by it
if ($f_subcategory) {
    if (is_numeric($f_subcategory)) {
        $cstmt = $conn->prepare("SELECT title FROM categories WHERE id = ?");
        $cstmt->bind_param("i", $f_subcategory);
        $cstmt->execute();
        $cres = $cstmt->get_result();
        if ($crow = $cres->fetch_assoc()) {
            $f_subcategory_name = $crow['title'];
        }
        $cstmt->close();
    } else {
        $f_subcategory_name = $f_subcategory;
    }
}

if ($f_county) {
    $safe_rc = $conn->real_escape_string($f_county);
    $global_filter .= " AND county LIKE '%$safe_rc%'";
}
if ($f_sector) {
    $safe_sec = $conn->real_escape_string($f_sector);
    $global_filter .= " AND role = '$safe_sec'";
}
if ($f_search) {
    $safe_q = $conn->real_escape_string($f_search);
    $global_filter .= " AND (title LIKE '%$safe_q%' OR org_name LIKE '%$safe_q%' OR story LIKE '%$safe_q%')";
}
if ($f_subcategory) {
    $safe_sub = $conn->real_escape_string($f_subcategory);
    $global_filter .= " AND (JSON_CONTAINS(IFNULL(service_chains, '[]'), JSON_QUOTE('$safe_sub')) 
                          OR JSON_CONTAINS(IFNULL(enabling_pillars, '[]'), JSON_QUOTE('$safe_sub')) 
                          OR JSON_CONTAINS(IFNULL(thematic_categories, '[]'), JSON_QUOTE('$safe_sub')))";
}
if ($f_rating) {
    $safe_rat = (int) $f_rating;
    if ($safe_rat >= 1 && $safe_rat <= 5) {
        $global_filter .= " AND id IN (
            SELECT item_id FROM ratings 
            WHERE item_type = 'submission' 
            GROUP BY item_id 
            HAVING ROUND(AVG(rating)) = $safe_rat
        )";
    }
}

function fetch_category_submissions($conn, $condition, $global_filter, $f_sort = 'most-viewed')
{
    // If global_filter has been modified beyond the default "is_verified = 1", remove the LIMIT
    $limit_clause = ($global_filter === "is_verified = 1") ? "LIMIT 10" : "";

    $order_clause = match ($f_sort) {
        'newest' => 's.created_at DESC',
        'oldest' => 's.created_at ASC',
        'az' => 's.title ASC',
        'top-rated' => 'avg_rat DESC, s.created_at DESC',
        default => 'view_count DESC, s.created_at DESC' // most-viewed
    };

    if ($f_sort === 'top-rated') {
        $sql = "SELECT s.id, s.org_name, s.title, s.story, s.created_at, s.role, s.service_chains, s.enabling_pillars, s.thematic_categories, s.county, s.user_id, IFNULL(s.view_count, 0) as view_count,
                       IFNULL(AVG(r.rating), 0) as avg_rat
                FROM submissions s 
                LEFT JOIN ratings r ON s.id = r.item_id AND r.item_type = 'submission'
                WHERE $global_filter AND ($condition) 
                GROUP BY s.id
                ORDER BY $order_clause $limit_clause";
    } else {
        $sql = "SELECT s.id, s.org_name, s.title, s.story, s.created_at, s.role, s.service_chains, s.enabling_pillars, s.thematic_categories, s.county, s.user_id, IFNULL(s.view_count, 0) as view_count 
                FROM submissions s WHERE $global_filter AND ($condition) 
                ORDER BY $order_clause $limit_clause";
    }

    $stmt = $conn->prepare($sql);
    $stmt->execute();
    return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
}

function count_category($conn, $condition, $global_filter)
{
    $sql = "SELECT COUNT(*) as total FROM submissions s WHERE $global_filter AND ($condition)";
    $stmt = $conn->prepare($sql);
    $stmt->execute();
    return $stmt->get_result()->fetch_assoc()['total'];
}

// Fetch all submissions matching global_filter
$all_submissions = fetch_category_submissions($conn, "1=1", $global_filter, $f_sort);

// Unique submissions by ID
$unique_subs = [];
$all_cat_ids = [];
foreach ($all_submissions as $sub) {
    if (isset($sub['id'])) {
        $sub_id = (int) $sub['id'];
        if (!isset($unique_subs[$sub_id])) {
            $chains = json_decode($sub['service_chains'] ?? '[]', true) ?: [];
            $pillars = json_decode($sub['enabling_pillars'] ?? '[]', true) ?: [];
            $themes = json_decode($sub['thematic_categories'] ?? '[]', true) ?: [];
            $cats = array_merge($themes, $pillars, $chains);
            $sub['cats'] = $cats;
            $unique_subs[$sub_id] = $sub;
            $all_cat_ids = array_merge($all_cat_ids, $cats);
        }
    }
}

// Build user_id → slug map for profile links
$slug_map = [];
$user_ids_for_slugs = array_unique(array_filter(array_column($all_submissions, 'user_id')));
if (!empty($user_ids_for_slugs)) {
    $slug_in = implode(',', array_fill(0, count($user_ids_for_slugs), '?'));
    $slug_stmt = $conn->prepare("SELECT id, slug FROM users WHERE id IN ($slug_in)");
    $slug_stmt->bind_param(str_repeat('i', count($user_ids_for_slugs)), ...$user_ids_for_slugs);
    $slug_stmt->execute();
    $slug_res = $slug_stmt->get_result();
    while ($slug_row = $slug_res->fetch_assoc()) {
        $slug_map[$slug_row['id']] = $slug_row['slug'];
    }
    $slug_stmt->close();
}

// Media counts
$sub_ids = array_keys($unique_subs);
$media_counts = [];

// Fetch batch ratings
$is_logged_in = isset($_SESSION['user_id']);
$current_user_id = $_SESSION['user_id'] ?? 0;
$batch_ratings = get_batch_ratings($conn, 'submission', $sub_ids);
$batch_user_ratings = $is_logged_in ? get_batch_user_ratings($conn, (int) $current_user_id, 'submission', $sub_ids) : [];

// Dash total views
$dash_total_views = 0;
$v_stmt = $conn->query("SELECT SUM(IFNULL(view_count, 0)) as c FROM submissions WHERE $global_filter");
if ($v_stmt) {
    $dash_total_views = (int) $v_stmt->fetch_assoc()['c'];
}
if (!empty($sub_ids)) {
    $in = implode(',', array_fill(0, count($sub_ids), '?'));
    $m_stmt = $conn->prepare("SELECT submission_id, media_type, COUNT(*) as cnt FROM media_uploads WHERE submission_id IN ($in) GROUP BY submission_id, media_type");
    $m_stmt->bind_param(str_repeat('i', count($sub_ids)), ...$sub_ids);
    $m_stmt->execute();
    $m_res = $m_stmt->get_result();
    while ($m_row = $m_res->fetch_assoc()) {
        $media_counts[$m_row['submission_id']][$m_row['media_type']] = $m_row['cnt'];
    }
}

// Category labels
$cat_map = [];
if (!empty($all_cat_ids)) {
    $valid_ids = array_unique(array_filter($all_cat_ids, 'is_numeric'));
    if (!empty($valid_ids)) {
        $placeholders = implode(',', array_fill(0, count($valid_ids), '?'));
        $cat_stmt = $conn->prepare("SELECT id, title FROM categories WHERE id IN ($placeholders)");
        $cat_stmt->bind_param(str_repeat('i', count($valid_ids)), ...$valid_ids);
        $cat_stmt->execute();
        $cat_res = $cat_stmt->get_result();
        while ($row = $cat_res->fetch_assoc()) {
            $cat_map[$row['id']] = $row['title'];
        }
    }
}

// Configuration for category cards
$color_classes = [
    'gold' => ['bg' => 'bg-[#D4AF37]', 'text' => 'text-[#D4AF37]', 'border' => 'border-[#D4AF37]/30'],
    'blue' => ['bg' => 'bg-blue-500', 'text' => 'text-blue-500', 'border' => 'border-blue-500/30'],
    'green' => ['bg' => 'bg-emerald-500', 'text' => 'text-emerald-500', 'border' => 'border-emerald-500/30'],
    'purple' => ['bg' => 'bg-purple-500', 'text' => 'text-purple-500', 'border' => 'border-purple-500/30']
];

$sections = [
    'service-chain' => [
        'label' => 'Service Chain',
        'icon' => 'fa-link',
        'color' => 'gold',
        'link' => 'service-chain.php',
        'condition' => "service_chains IS NOT NULL AND service_chains != '[]'",
        'count' => count_category($conn, "service_chains IS NOT NULL AND service_chains != '[]'", $global_filter),
        'subs' => []
    ],
    'enabling-pillars' => [
        'label' => 'Enabling Pillars',
        'icon' => 'fa-columns',
        'color' => 'blue',
        'link' => 'enabling-pillars.php',
        'condition' => "enabling_pillars IS NOT NULL AND enabling_pillars != '[]'",
        'count' => count_category($conn, "enabling_pillars IS NOT NULL AND enabling_pillars != '[]'", $global_filter),
        'subs' => []
    ],
    'barriers' => [
        'label' => 'Barriers',
        'icon' => 'fa-shield-halved',
        'color' => 'green',
        'link' => 'barriers.php',
        'condition' => "thematic_categories IS NOT NULL AND thematic_categories != '[]'",
        'count' => count_category($conn, "thematic_categories IS NOT NULL AND thematic_categories != '[]'", $global_filter),
        'subs' => []
    ],
    'sanitation-status' => [
        'label' => 'Sanitation Status',
        'icon' => 'fa-chart-pie',
        'color' => 'purple',
        'link' => '#',
        'condition' => null,
        'count' => 0,
        'subs' => []
    ]
];

// Distribute submissions for top-sub-category preview using the already fetched $all_submissions
foreach ($all_submissions as $sub) {
    if (isset($sub['service_chains']) && $sub['service_chains'] != '[]') {
        $sections['service-chain']['subs'][] = $sub;
    }
    if (isset($sub['enabling_pillars']) && $sub['enabling_pillars'] != '[]') {
        $sections['enabling-pillars']['subs'][] = $sub;
    }
    if (isset($sub['thematic_categories']) && $sub['thematic_categories'] != '[]') {
        $sections['barriers']['subs'][] = $sub;
    }
    // Sanitation Status is a "coming soon" category – no distribution needed
}

// Adjust counts based on the chosen metric
$metric = $_GET['metric'] ?? 'submissions';
$metric_display_name = 'Submissions';
if ($metric === 'organisations')
    $metric_display_name = 'Organisations';
if ($metric === 'counties')
    $metric_display_name = 'Counties';

foreach ($sections as $slug => &$sec) {
    if ($metric === 'organisations') {
        $unique_orgs = [];
        foreach ($sec['subs'] as $sub) {
            $org = trim($sub['org_name'] ?? '');
            if ($org)
                $unique_orgs[$org] = true;
        }
        $sec['count'] = count($unique_orgs);
    } elseif ($metric === 'counties') {
        $unique_counties = [];
        foreach ($sec['subs'] as $sub) {
            foreach (array_map('trim', explode(',', $sub['county'] ?? '')) as $c) {
                if ($c)
                    $unique_counties[$c] = true;
            }
        }
        $sec['count'] = count($unique_counties);
    }
}
unset($sec);

// Total verified submissions
$total_verified = (int) $conn->query("SELECT COUNT(*) as c FROM submissions WHERE $global_filter")->fetch_assoc()['c'];

// ===== DASHBOARD DATA =====
// KPI 1: Total verified submissions
$dash_total = $total_verified;

// KPI 2: Total distinct organisations
$dash_orgs = (int) $conn->query("SELECT COUNT(DISTINCT org_name) as c FROM submissions WHERE $global_filter")->fetch_assoc()['c'];

// KPI 3 + County heatmap data
$county_result = $conn->query("SELECT county FROM submissions WHERE $global_filter AND county IS NOT NULL AND county != ''");
$county_counts_dash = [];
while ($crow = $county_result->fetch_assoc()) {
    foreach (array_map('trim', explode(',', $crow['county'])) as $c) {
        if ($c !== '')
            $county_counts_dash[$c] = ($county_counts_dash[$c] ?? 0) + 1;
    }
}
arsort($county_counts_dash);
$dash_counties = count($county_counts_dash);

// KPI 4: Submissions with avg rating >= 4.0
$top_rated_q = $conn->query("SELECT COUNT(*) as c FROM (
    SELECT r.item_id FROM ratings r
    JOIN submissions s ON r.item_id = s.id AND $global_filter
    WHERE r.item_type = 'submission'
    GROUP BY r.item_id HAVING AVG(r.rating) >= 4
) AS tr");
$dash_top_rated = $top_rated_q ? (int) $top_rated_q->fetch_assoc()['c'] : 0;

// Monthly trend (last 12 months)
$trend_q = $conn->query("SELECT DATE_FORMAT(created_at, '%Y-%m') as m, COUNT(*) as c
    FROM submissions WHERE $global_filter AND created_at >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
    GROUP BY m ORDER BY m");
$trend_labels = [];
$trend_values = [];
while ($trow = $trend_q->fetch_assoc()) {
    $trend_labels[] = date("M 'y", strtotime($trow['m'] . '-01'));
    $trend_values[] = (int) $trow['c'];
}

// Sector distribution (from org_sector JSON, fallback to role)
$sector_q = $conn->query("SELECT org_sector, role FROM submissions WHERE $global_filter");
$sector_counts_dash = [];
while ($srow = $sector_q->fetch_assoc()) {
    $sector = 'Other';
    if (!empty($srow['org_sector'])) {
        $sd = json_decode($srow['org_sector'], true);
        if (!empty($sd['sector']))
            $sector = $sd['sector'];
    } elseif (!empty($srow['role'])) {
        $sector = $srow['role'];
    }
    $sector_counts_dash[$sector] = ($sector_counts_dash[$sector] ?? 0) + 1;
}
arsort($sector_counts_dash);

// Rating distribution (1-5 stars)
$rating_q = $conn->query("SELECT ROUND(sub_avg) as stars, COUNT(*) as c FROM (
    SELECT r.item_id, AVG(r.rating) as sub_avg FROM ratings r
    JOIN submissions s ON r.item_id = s.id AND $global_filter
    WHERE r.item_type = 'submission' GROUP BY r.item_id
) AS rd GROUP BY stars ORDER BY stars");
$rating_values = [0, 0, 0, 0, 0];
if ($rating_q) {
    while ($rrow = $rating_q->fetch_assoc()) {
        $s = (int) $rrow['stars'];
        if ($s >= 1 && $s <= 5)
            $rating_values[$s - 1] = (int) $rrow['c'];
    }
}

// Category distribution
$cat_dist = [];
$sc_q = $conn->query("SELECT service_chains FROM submissions WHERE $global_filter AND service_chains IS NOT NULL AND service_chains != '' AND service_chains != '[]'");
while ($r = $sc_q->fetch_assoc()) {
    foreach (json_decode($r['service_chains'], true) ?: [] as $v) {
        $cat_dist[$v] = ($cat_dist[$v] ?? 0) + 1;
    }
}
$ep_q = $conn->query("SELECT enabling_pillars FROM submissions WHERE $global_filter AND enabling_pillars IS NOT NULL AND enabling_pillars != '' AND enabling_pillars != '[]'");
while ($r = $ep_q->fetch_assoc()) {
    foreach (json_decode($r['enabling_pillars'], true) ?: [] as $v) {
        $cat_dist[$v] = ($cat_dist[$v] ?? 0) + 1;
    }
}
arsort($cat_dist);

// A6: Recent Submissions
$recent_subs = [];
$r_stmt = $conn->query("SELECT * FROM submissions WHERE $global_filter ORDER BY created_at DESC LIMIT 5");
if ($r_stmt) {
    while ($row = $r_stmt->fetch_assoc()) {
        $recent_subs[] = $row;
    }
}

// Top 10 Organizations
$top_orgs = [];
$org_q = $conn->query("SELECT org_name, COUNT(*) as submission_count FROM submissions WHERE $global_filter AND org_name IS NOT NULL AND org_name != '' GROUP BY org_name ORDER BY submission_count DESC LIMIT 10");
if ($org_q) {
    while ($orow = $org_q->fetch_assoc()) {
        $top_orgs[] = $orow;
    }
}
$max_org_count = !empty($top_orgs) ? (int) $top_orgs[0]['submission_count'] : 1;

// A6: Top Rated Submissions
$top_rated_subs = [];
$t_stmt = $conn->query("
    SELECT s.*, ROUND(AVG(r.rating), 1) as avg_rating 
    FROM submissions s 
    JOIN ratings r ON s.id = r.item_id AND r.item_type = 'submission' 
    WHERE $global_filter 
    GROUP BY s.id 
    ORDER BY avg_rating DESC, s.created_at DESC 
    LIMIT 5
");
if ($t_stmt) {
    while ($row = $t_stmt->fetch_assoc()) {
        $top_rated_subs[] = $row;
    }
}

?>
<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Submissions Dashboard | SaniBook Kenya</title>
    <meta name="description"
        content="Explore verified sanitation submissions categorized by Service Chain, Enabling Pillars, Sanitation Status, and Barriers.">
    <link rel="preconnect" href="https://fonts.googleapis.com">
    <link rel="preconnect" href="https://fonts.gstatic.com" crossorigin>
    <link
        href="https://fonts.googleapis.com/css2?family=Montserrat:wght@700;900&family=Inter:wght@400;500;600;700&display=swap"
        rel="stylesheet">
    <script src="https://cdn.tailwindcss.com"></script>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.5.1/css/all.min.css" />
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    <!-- AmCharts 4 Map Dependencies -->
    <script src="https://cdn.amcharts.com/lib/4/core.js"></script>
    <script src="https://cdn.amcharts.com/lib/4/maps.js"></script>
    <script src="https://cdn.amcharts.com/lib/4/geodata/kenyaLow.js"></script>
    <script src="https://cdn.amcharts.com/lib/4/themes/animated.js"></script>
    <link rel="icon" href="images/saniBook logo.png" type="image/png">
    <script>
        tailwind.config = {
            theme: {
                extend: {
                    colors: {
                        wash: { teal: '#008080', navy: '#001F3F', gold: '#D4AF37' },
                        brand: { dark: '#023047', teal: '#219EBC', gold: '#D4AF37' }
                    },
                    fontFamily: {
                        sans: ['Inter', 'system-ui', 'sans-serif'],
                        heading: ['Montserrat', 'sans-serif'],
                    }
                }
            }
        }
    </script>
    <!-- Rating CSS -->
    <style>
        .star-rating-widget {
            display: inline-flex;
            align-items: center;
            gap: 8px;
        }

        .star-rating-stars {
            display: flex;
            gap: 2px;
        }

        .star-rating-stars .star {
            color: rgba(255, 255, 255, 0.2);
            font-size: 14px;
            transition: all 0.2s;
        }

        .star-rating-stars .star.filled,
        .star-rating-stars .star.user-rated {
            color: #D4AF37;
        }

        .star-rating-info {
            font-size: 11px;
            color: rgba(255, 255, 255, 0.6);
            font-weight: 600;
        }

        .tab-panel {
            display: none;
        }

        .tab-panel.active {
            display: block;
        }

        .tab-btn.active {
            border-bottom-color: currentColor;
            font-weight: 700;
        }

        /* === Glassmorphism KPI cards === */
        .kpi-glass {
            background: rgba(255, 255, 255, 0.10);
            backdrop-filter: blur(14px);
            -webkit-backdrop-filter: blur(14px);
            border: 1px solid rgba(255, 255, 255, 0.18);
            transition: all 0.25s ease;
        }

        .kpi-glass:hover {
            background: rgba(255, 255, 255, 0.18);
            border-color: rgba(255, 255, 255, 0.38);
            transform: translateY(-3px);
            box-shadow: 0 14px 36px rgba(0, 0, 0, 0.28);
        }

        /* === Glassmorphism Result cards === */
        .glass-card {
            background: rgba(10, 30, 60, 0.45);
            backdrop-filter: blur(16px);
            -webkit-backdrop-filter: blur(16px);
            border: 1px solid rgba(255, 255, 255, 0.10);
            transition: all 0.25s ease;
        }

        .glass-card:hover {
            background: rgba(10, 30, 60, 0.60);
            border-color: rgba(0, 200, 180, 0.35);
            transform: translateY(-3px);
            box-shadow: 0 16px 40px rgba(0, 0, 0, 0.35);
        }
    </style>
</head>

<body id="top" class="bg-slate-50 text-slate-900 antialiased">

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

    <!-- Page Header -->
    <header class="pt-24 pb-8 border-b border-slate-200"
        style="background:linear-gradient(135deg,#012a4a 0%,#013a5e 45%,#01497c 80%,#023e6e 100%);">
        <div class="max-w-7xl mx-auto px-4">
            <div class="flex flex-col md:flex-row md:items-center md:justify-between gap-4">
                <div>
                    <p class="text-xs font-bold uppercase tracking-widest text-teal-300 mb-1">
                        <i class="fa-solid fa-table-columns mr-1"></i> Dashboard
                    </p>
                    <?php if ($f_county || $f_sector || $f_search || $f_subcategory || $f_rating): ?>
                        <h1 class="font-heading font-black text-3xl md:text-4xl text-white">Filtered Results</h1>
                        <div
                            class="flex flex-col lg:flex-row lg:items-center gap-4 mt-4 bg-white/5 p-3 rounded-lg border border-white/10">
                            <div class="flex flex-wrap gap-2 items-center flex-1">
                                <span class="text-sm font-semibold text-blue-200/70 mr-1">Filters:</span>
                                <?php if ($f_search): ?>
                                    <span
                                        class="bg-blue-50 text-blue-700 border border-blue-200 pl-2.5 pr-1 py-1 rounded-md text-xs font-bold flex items-center gap-1">Search:
                                        <?php echo htmlspecialchars($f_search); ?> <a
                                            href="submitted.php?<?php echo http_build_query(array_diff_key($_GET, ['q' => '', 'page' => ''])); ?>"
                                            class="text-blue-400 hover:text-blue-700"><i
                                                class="fa-solid fa-xmark ml-1"></i></a></span>
                                <?php endif; ?>
                                <?php if ($f_county): ?>
                                    <span
                                        class="bg-teal-50 text-teal-700 border border-teal-200 pl-2.5 pr-1 py-1 rounded-md text-xs font-bold flex items-center gap-1"><i
                                            class="fa-solid fa-location-dot mr-1"></i>
                                        <?php echo htmlspecialchars($f_county); ?> <a
                                            href="submitted.php?<?php echo http_build_query(array_diff_key($_GET, ['county' => '', 'page' => ''])); ?>"
                                            class="text-teal-400 hover:text-teal-700"><i
                                                class="fa-solid fa-xmark ml-1"></i></a></span>
                                <?php endif; ?>
                                <?php if ($f_sector): ?>
                                    <span
                                        class="bg-purple-50 text-purple-700 border border-purple-200 pl-2.5 pr-1 py-1 rounded-md text-xs font-bold flex items-center gap-1"><i
                                            class="fa-solid fa-building mr-1"></i> <?php echo htmlspecialchars($f_sector); ?> <a
                                            href="submitted.php?<?php echo http_build_query(array_diff_key($_GET, ['sector' => '', 'page' => ''])); ?>"
                                            class="text-purple-400 hover:text-purple-700"><i
                                                class="fa-solid fa-xmark ml-1"></i></a></span>
                                <?php endif; ?>
                                <?php if ($f_subcategory): ?>
                                    <span
                                        class="bg-orange-50 text-orange-700 border border-orange-200 pl-2.5 pr-1 py-1 rounded-md text-xs font-bold flex items-center gap-1"><i
                                            class="fa-solid fa-tag mr-1"></i>
                                        <?php echo htmlspecialchars($f_subcategory_name); ?> <a
                                            href="submitted.php?<?php echo http_build_query(array_diff_key($_GET, ['subcategory' => '', 'page' => ''])); ?>"
                                            class="text-orange-400 hover:text-orange-700"><i
                                                class="fa-solid fa-xmark ml-1"></i></a></span>
                                <?php endif; ?>
                                <?php if ($f_rating): ?>
                                    <span
                                        class="bg-yellow-50 text-yellow-700 border border-yellow-200 pl-2.5 pr-1 py-1 rounded-md text-xs font-bold flex items-center gap-1"><i
                                            class="fa-solid fa-star mr-1"></i> <?php echo htmlspecialchars($f_rating); ?>
                                        Star(s) <a
                                            href="submitted.php?<?php echo http_build_query(array_diff_key($_GET, ['rating' => '', 'page' => ''])); ?>"
                                            class="text-yellow-400 hover:text-yellow-700"><i
                                                class="fa-solid fa-xmark ml-1"></i></a></span>
                                <?php endif; ?>
                                <a href="submitted.php"
                                    class="text-xs text-slate-400 hover:text-red-500 transition-colors ml-2 font-medium">Clear
                                    All</a>
                            </div>
                            <form id="sortForm" method="GET" action="submitted.php"
                                class="flex items-center gap-2 shrink-0">
                                <?php foreach ($_GET as $key => $value): ?>
                                    <?php if ($key !== 'sort' && $key !== 'page'): ?>
                                        <input type="hidden" name="<?php echo htmlspecialchars($key); ?>"
                                            value="<?php echo htmlspecialchars($value); ?>">
                                    <?php endif; ?>
                                <?php endforeach; ?>
                                <label for="sort"
                                    class="text-[10px] font-bold text-blue-200/60 uppercase tracking-wider hidden sm:block">Sort
                                    By:</label>
                                <select name="sort" id="sort" onchange="document.getElementById('sortForm').submit();"
                                    class="bg-white/10 border border-white/20 text-white placeholder-white/50 text-xs rounded-lg focus:ring-teal-500 focus:border-teal-500 block px-2.5 py-1.5 focus:outline-none shadow-sm cursor-pointer [&>option]:text-slate-900">
                                    <option value="most-viewed" <?php echo $f_sort == 'most-viewed' ? 'selected' : ''; ?>>Most
                                        Viewed</option>
                                    <option value="newest" <?php echo $f_sort == 'newest' ? 'selected' : ''; ?>>Newest First
                                    </option>
                                    <option value="oldest" <?php echo $f_sort == 'oldest' ? 'selected' : ''; ?>>Oldest First
                                    </option>
                                    <option value="top-rated" <?php echo $f_sort == 'top-rated' ? 'selected' : ''; ?>>Highest
                                        Rated</option>
                                    <option value="az" <?php echo $f_sort == 'az' ? 'selected' : ''; ?>>A-Z (Title)</option>
                                </select>
                            </form>
                        </div>
                    <?php else: ?>
                        <h1 class="font-heading font-black text-3xl md:text-4xl text-white">Submissions Overview</h1>
                        <div class="flex flex-col sm:flex-row sm:items-center justify-between gap-4 mt-3">
                            <p class="text-blue-200/80 text-sm">Latest verified submissions across all SaniBook categories.
                            </p>
                            <form id="sortForm" method="GET" action="submitted.php" class="flex items-center gap-2">
                                <label for="sort"
                                    class="text-[10px] font-bold text-blue-200/60 uppercase tracking-wider hidden sm:block">Sort
                                    By:</label>
                                <select name="sort" id="sort" onchange="document.getElementById('sortForm').submit();"
                                    class="bg-white/10 border border-white/20 text-white placeholder-white/50 text-xs rounded-lg focus:ring-teal-500 focus:border-teal-500 block px-2.5 py-1.5 focus:outline-none shadow-sm cursor-pointer [&>option]:text-slate-900">
                                    <option value="most-viewed" <?php echo $f_sort == 'most-viewed' ? 'selected' : ''; ?>>Most
                                        Viewed</option>
                                    <option value="newest" <?php echo $f_sort == 'newest' ? 'selected' : ''; ?>>Newest First
                                    </option>
                                    <option value="oldest" <?php echo $f_sort == 'oldest' ? 'selected' : ''; ?>>Oldest First
                                    </option>
                                    <option value="top-rated" <?php echo $f_sort == 'top-rated' ? 'selected' : ''; ?>>Highest
                                        Rated</option>
                                    <option value="az" <?php echo $f_sort == 'az' ? 'selected' : ''; ?>>A-Z (Title)</option>
                                </select>
                            </form>
                        </div>
                    <?php endif; ?>
                </div>
                <div class="flex gap-3 shrink-0">
                    <?php if (isset($_SESSION['user_id'])): ?>
                        <a href="submit-data.php"
                            class="inline-flex items-center gap-2 px-5 py-2.5 bg-white/10 backdrop-blur border border-white/30 text-white font-bold rounded-lg hover:bg-white/20 transition-colors text-sm">
                            <i class="fa-solid fa-cloud-arrow-up"></i> Submit Data
                        </a>
                    <?php else: ?>
                        <a href="login.php"
                            class="inline-flex items-center gap-2 px-5 py-2.5 bg-white/10 backdrop-blur border border-white/30 text-white font-bold rounded-lg hover:bg-white/20 transition-colors text-sm">
                            <i class="fa-solid fa-right-to-bracket"></i> Login to Submit
                        </a>
                    <?php endif; ?>
                    <a href="explore.php"
                        class="inline-flex items-center gap-2 px-5 py-2.5 bg-white/10 backdrop-blur border border-white/30 text-white font-bold rounded-lg hover:bg-white/20 transition-colors text-sm">
                        <i class="fa-solid fa-users"></i> Directory
                    </a>
                </div>
            </div>

            <!-- KPI Ribbon (glassmorphism) -->
            <div class="grid grid-cols-2 sm:grid-cols-3 lg:grid-cols-5 gap-4 mb-8 mt-6">
                <a href="#all-submissions" class="kpi-glass block rounded-xl p-5 text-white cursor-pointer">
                    <p class="text-white/60 text-[10px] font-bold uppercase tracking-widest mb-1"><i
                            class="fa-solid fa-database mr-1"></i>Total Submissions</p>
                    <p class="text-3xl font-heading font-black"><?php echo number_format($dash_total); ?></p>
                </a>
                <a href="#all-submissions" class="kpi-glass block rounded-xl p-5 text-white cursor-pointer">
                    <p class="text-white/60 text-[10px] font-bold uppercase tracking-widest mb-1"><i
                            class="fa-solid fa-eye mr-1"></i>Total Views</p>
                    <p class="text-3xl font-heading font-black"><?php echo number_format($dash_total_views); ?></p>
                </a>
                <a href="#top-organisations" class="kpi-glass block rounded-xl p-5 text-white cursor-pointer">
                    <p class="text-white/60 text-[10px] font-bold uppercase tracking-widest mb-1"><i
                            class="fa-solid fa-building mr-1"></i>Organisations</p>
                    <p class="text-3xl font-heading font-black"><?php echo number_format($dash_orgs); ?></p>
                </a>
                <a href="#geo-reach" class="kpi-glass block rounded-xl p-5 text-white cursor-pointer">
                    <p class="text-white/60 text-[10px] font-bold uppercase tracking-widest mb-1"><i
                            class="fa-solid fa-map mr-1"></i>Active Counties</p>
                    <p class="text-3xl font-heading font-black"><?php echo number_format($dash_counties); ?><span
                            class="text-base font-normal text-white/40"> / 47</span></p>
                </a>
                <a href="#trending"
                    class="kpi-glass block rounded-xl p-5 text-white cursor-pointer col-span-2 sm:col-span-1 lg:col-span-1">
                    <p class="text-white/60 text-[10px] font-bold uppercase tracking-widest mb-1"><i
                            class="fa-solid fa-star mr-1"></i>Top Rated</p>
                    <p class="text-3xl font-heading font-black"><?php echo number_format($dash_top_rated); ?></p>
                    <p class="text-[10px] text-white/40 mt-0.5">Avg &ge; 4.0 stars</p>
                </a>
            </div>

            <!-- Hero Search Bar -->
            <div class="mt-8 relative z-50">
                <div class="relative max-w-3xl">
                    <div class="absolute inset-y-0 left-0 pl-4 flex items-center pointer-events-none">
                        <i class="fa-solid fa-magnifying-glass text-slate-400 text-lg"></i>
                    </div>
                    <input type="text" id="heroSearch" autocomplete="off"
                        class="block w-full pl-12 pr-4 py-4 bg-white border border-slate-300 rounded-xl leading-5 focus:outline-none focus:ring-2 focus:ring-teal-500 focus:border-teal-500 sm:text-lg transition-shadow shadow-sm placeholder-slate-400 text-slate-900"
                        placeholder="Search submissions, organisations, or counties...">
                    <div class="absolute inset-y-0 right-0 pr-4 flex items-center pointer-events-none hidden"
                        id="searchSpinner">
                        <i class="fa-solid fa-circle-notch fa-spin text-teal-600"></i>
                    </div>
                </div>

                <!-- Search Results Dropdown -->
                <div id="searchResults"
                    class="hidden absolute top-full left-0 mt-2 w-full max-w-3xl bg-white rounded-xl shadow-2xl border border-slate-200 overflow-hidden divide-y divide-slate-100 max-h-[80vh] overflow-y-auto">
                    <!-- Results populated by JS -->
                </div>
            </div>

            <!-- "I Want To..." Intent Cards -->
            <div class="mt-10 mb-0 pb-8">
                <p class="text-sm font-bold text-white/60 uppercase tracking-wider mb-4">I want to...</p>
                <div class="grid grid-cols-1 sm:grid-cols-2 lg:grid-cols-3 gap-4">
                    <a href="service-chain.php" class="group kpi-glass rounded-xl p-5 flex items-start gap-4">
                        <div class="w-12 h-12 rounded-lg flex items-center justify-center text-xl shrink-0 group-hover:scale-110 transition-transform"
                            style="background:rgba(0,128,128,0.25);color:#4DD9D9;">
                            <i class="fa-solid fa-hammer"></i>
                        </div>
                        <div>
                            <h3 class="font-bold text-white mb-1">Explore the technologies</h3>
                            <p class="text-xs text-blue-200/70 leading-relaxed">Explore technologies across the service
                                chain (containment, treatment, etc.)</p>
                        </div>
                    </a>

                    <a href="enabling-pillars.php?sub=research"
                        class="group kpi-glass rounded-xl p-5 flex items-start gap-4">
                        <div class="w-12 h-12 rounded-lg flex items-center justify-center text-xl shrink-0 group-hover:scale-110 transition-transform"
                            style="background:rgba(234,106,54,0.2);color:#F6A97A;">
                            <i class="fa-solid fa-book-open-reader"></i>
                        </div>
                        <div>
                            <h3 class="font-bold text-white mb-1">Research & Learn</h3>
                            <p class="text-xs text-blue-200/70 leading-relaxed">Browse the data related to on-site
                                sanitation.</p>
                        </div>
                    </a>

                    <a href="enabling-pillars.php?sub=financing"
                        class="group kpi-glass rounded-xl p-5 flex items-start gap-4">
                        <div class="w-12 h-12 rounded-lg flex items-center justify-center text-xl shrink-0 group-hover:scale-110 transition-transform"
                            style="background:rgba(212,175,55,0.2);color:#D4AF37;">
                            <i class="fa-solid fa-hand-holding-dollar"></i>
                        </div>
                        <div>
                            <h3 class="font-bold text-white mb-1">Funding & Investment</h3>
                            <p class="text-xs text-blue-200/70 leading-relaxed">Discover organizations involved in
                                funding
                                and investment.</p>
                        </div>
                    </a>

                    <a href="explore.php" class="group kpi-glass rounded-xl p-5 flex items-start gap-4">
                        <div class="w-12 h-12 rounded-lg flex items-center justify-center text-xl shrink-0 group-hover:scale-110 transition-transform"
                            style="background:rgba(99,102,241,0.2);color:#A5B4FC;">
                            <i class="fa-solid fa-building-user"></i>
                        </div>
                        <div>
                            <h3 class="font-bold text-white mb-1">Interact with Sanitation Actors</h3>
                            <p class="text-xs text-blue-200/70 leading-relaxed">Search our directory of verified
                                sanitation organisations and experts.</p>
                        </div>
                    </a>

                    <a href="#geo-reach" class="group kpi-glass rounded-xl p-5 flex items-start gap-4">
                        <div class="w-12 h-12 rounded-lg flex items-center justify-center text-xl shrink-0 group-hover:scale-110 transition-transform"
                            style="background:rgba(100,200,150,0.18);color:#6EE7B7;">
                            <i class="fa-solid fa-map-location-dot"></i>
                        </div>
                        <div>
                            <h3 class="font-bold text-white mb-1">Explore data per county</h3>
                            <p class="text-xs text-blue-200/70 leading-relaxed">Use the interactive map to find data
                                specific to your area.</p>
                        </div>
                    </a>

                    <a href="#all-submissions" class="group kpi-glass rounded-xl p-5 flex items-start gap-4">
                        <div class="w-12 h-12 rounded-lg flex items-center justify-center text-xl shrink-0 group-hover:scale-110 transition-transform"
                            style="background:rgba(59,130,246,0.2);color:#93C5FD;">
                            <i class="fa-solid fa-layer-group"></i>
                        </div>
                        <div>
                            <h3 class="font-bold text-white mb-1">Browse All Data</h3>
                            <p class="text-xs text-blue-200/70 leading-relaxed">View all submissions grouped by our
                                standard categorisation.</p>
                        </div>
                    </a>
                </div>
            </div>
        </div>
    </header>

    <!-- ===== DASHBOARD SECTION ===== -->
    <section class="bg-gradient-to-b from-[#001F3F] to-[#00304a] py-10">
        <div class="max-w-7xl mx-auto px-4">



            <!-- Row 2: Trend + County Grid -->
            <div class="grid grid-cols-1 lg:grid-cols-2 gap-6 mb-6">
                <!-- Submission Trend -->
                <div class="bg-white/10 backdrop-blur rounded-xl p-5 border border-white/10">
                    <h3 class="text-white font-bold text-sm mb-4"><i
                            class="fa-solid fa-chart-line mr-2 text-[#D4AF37]"></i>Monthly Submission Trend</h3>
                    <canvas id="trendChart" height="220"></canvas>
                </div>
                <!-- County Reach Grid -->
                <div id="geo-reach" class="bg-white/10 backdrop-blur rounded-xl p-5 border border-white/10">
                    <h3 class="text-white font-bold text-sm mb-3"><i
                            class="fa-solid fa-map-location-dot mr-2 text-[#D4AF37]"></i>Geographic Reach</h3>
                    <div id="kenyaMap" style="width: 100%; height: 350px;"></div>
                    <p class="text-white/30 text-[10px] mt-2 text-center">Hover for details &middot; Gold = submission
                        density</p>
                </div>
            </div>

            <!-- A5: Navigational Category Cards -->
            <div class="mb-10" id="category-browse">
                <h3 class="text-white font-heading font-black text-xl mb-4">Browse by Category</h3>
                <div class="grid grid-cols-1 sm:grid-cols-2 lg:grid-cols-4 gap-4">
                    <?php foreach ($sections as $slug => $sec):
                        $cc = $color_classes[$sec['color']];

                        $sub_cat_counts = [];
                        $cat_key = '';
                        if ($slug == 'service-chain')
                            $cat_key = 'service_chains';
                        elseif ($slug == 'enabling-pillars')
                            $cat_key = 'enabling_pillars';
                        elseif ($slug == 'barriers')
                            $cat_key = 'thematic_categories';

                        if ($cat_key) {
                            foreach ($sec['subs'] as $sub) {
                                $json_cats = json_decode($sub[$cat_key] ?? '[]', true) ?: [];

                                if ($metric === 'organisations') {
                                    $org = trim($sub['org_name'] ?? '');
                                    foreach ($json_cats as $c_id) {
                                        if (!isset($sub_cat_counts[$c_id]))
                                            $sub_cat_counts[$c_id] = [];
                                        if ($org)
                                            $sub_cat_counts[$c_id][$org] = true;
                                    }
                                } elseif ($metric === 'counties') {
                                    $c_list = array_map('trim', explode(',', $sub['county'] ?? ''));
                                    foreach ($json_cats as $c_id) {
                                        if (!isset($sub_cat_counts[$c_id]))
                                            $sub_cat_counts[$c_id] = [];
                                        foreach ($c_list as $c) {
                                            if ($c)
                                                $sub_cat_counts[$c_id][$c] = true;
                                        }
                                    }
                                } else {
                                    foreach ($json_cats as $c_id) {
                                        $sub_cat_counts[$c_id] = ($sub_cat_counts[$c_id] ?? 0) + 1;
                                    }
                                }
                            }

                            if ($metric !== 'submissions') {
                                foreach ($sub_cat_counts as $k => $arr) {
                                    $sub_cat_counts[$k] = count($arr);
                                }
                            }
                        }
                        arsort($sub_cat_counts);
                        $top3 = array_slice($sub_cat_counts, 0, 3, true);
                        ?>
                        <div
                            class="group bg-white/10 backdrop-blur border border-white/10 rounded-xl p-5 hover:bg-white/20 transition-all flex flex-col h-full relative overflow-hidden">
                            <!-- Subtle Glow -->
                            <div
                                class="absolute -top-10 -right-10 w-24 h-24 <?php echo $cc['bg']; ?> blur-3xl opacity-20 rounded-full group-hover:opacity-40 transition-opacity pointer-events-none">
                            </div>

                            <a href="<?php echo $sec['link']; ?>" class="block relative z-10 mb-4 cursor-pointer">
                                <?php if ($sec['condition'] == null): ?>
                                    <span
                                        class="absolute top-0 right-0 bg-indigo-500 text-white text-[9px] font-bold px-2 py-0.5 rounded-full uppercase tracking-wider backdrop-blur border border-indigo-400/50 shadow-sm z-20">Coming
                                        Soon</span>
                                <?php endif; ?>
                                <div class="flex items-start justify-between mb-4">
                                    <div
                                        class="w-10 h-10 rounded-lg <?php echo $cc['bg']; ?> <?php echo $cc['text']; ?> flex items-center justify-center text-lg">
                                        <i class="fa-solid <?php echo $sec['icon']; ?>"></i>
                                    </div>
                                    <div class="text-white/40 group-hover:text-white transition-colors">
                                        <i class="fa-solid fa-arrow-right -rotate-45"></i>
                                    </div>
                                </div>

                                <h4
                                    class="text-white font-bold text-lg mb-1 group-hover:<?php echo $cc['text']; ?> transition-colors">
                                    <?php echo $sec['label']; ?>
                                </h4>
                                <p class="text-white/60 text-[10px] font-bold uppercase tracking-widest">
                                    <?php echo number_format($sec['count']); ?>     <?php echo $metric_display_name; ?>
                                </p>
                            </a>

                            <div class="space-y-0.5 mt-auto relative z-10">
                                <?php if (empty($top3) && $sec['condition'] == null): ?>
                                    <div class="text-white/40 text-[10px] italic bg-white/5 p-2 rounded">Coming soon. We are
                                        developing this section.</div>
                                <?php elseif (empty($top3)): ?>
                                    <div class="text-white/40 text-[10px] italic bg-white/5 p-2 rounded">No sub-categories
                                        found.</div>
                                <?php else: ?>
                                    <?php foreach ($top3 as $c_id => $sc_count):
                                        $name = is_numeric($c_id) ? ($cat_map[$c_id] ?? "Unknown") : $c_id;
                                        ?>
                                        <a href="submitted.php?subcategory=<?php echo urlencode($c_id); ?>"
                                            class="flex justify-between items-center text-[11px] group/sub cursor-pointer hover:bg-white/5 px-2 py-1.5 -mx-2 rounded transition-colors">
                                            <span class="text-white/80 group-hover/sub:text-white truncate w-[75%]"
                                                title="<?php echo htmlspecialchars($name); ?>"><?php echo htmlspecialchars($name); ?></span>
                                            <span
                                                class="text-white/50 bg-white/10 group-hover/sub:bg-white/20 group-hover/sub:text-white/90 font-medium px-1.5 py-0.5 rounded text-[9px] transition-colors"><?php echo $sc_count; ?></span>
                                        </a>
                                    <?php endforeach; ?>
                                    <?php if (count($sub_cat_counts) > 3): ?>
                                        <div
                                            class="text-[10px] text-white/40 mt-2 font-medium italic group-hover:text-white/70 transition-colors pt-2 border-t border-white/10">
                                            + <?php echo count($sub_cat_counts) - 3; ?> more sub-categories</div>
                                    <?php endif; ?>
                                <?php endif; ?>
                            </div>
                        </div>
                    <?php endforeach; ?>
                </div>
            </div>

            <!-- Top 10 Organisations Leaderboard -->
            <div class="mb-10" id="top-organisations">
                <h3 class="text-white font-heading font-black text-xl mb-4"><i
                        class="fa-solid fa-building mr-2 text-[#D4AF37]"></i>Top Organisations</h3>
                <?php if (empty($top_orgs)): ?>
                    <div
                        class="bg-white/10 backdrop-blur rounded-xl border border-white/10 p-6 text-center text-white/50 text-sm">
                        No organisations found.</div>
                <?php else: ?>
                    <div class="grid grid-cols-1 sm:grid-cols-2 gap-3">
                        <?php foreach ($top_orgs as $idx => $org_item):
                            $bar_pct = round(($org_item['submission_count'] / $max_org_count) * 100);
                            $rank = $idx + 1;
                            ?>
                            <a href="submitted.php?q=<?php echo urlencode($org_item['org_name']); ?>"
                                class="group bg-white/10 backdrop-blur border border-white/10 rounded-xl p-4 hover:bg-white/20 transition-all flex items-center gap-4">
                                <div
                                    class="w-8 h-8 rounded-full bg-[#D4AF37]/20 text-[#D4AF37] flex items-center justify-center text-sm font-black shrink-0">
                                    <?php echo $rank; ?>
                                </div>
                                <div class="flex-1 min-w-0">
                                    <p class="text-white font-bold text-sm truncate group-hover:text-[#D4AF37] transition-colors"
                                        title="<?php echo htmlspecialchars($org_item['org_name']); ?>">
                                        <?php echo htmlspecialchars($org_item['org_name']); ?>
                                    </p>
                                    <div class="mt-1.5 h-1.5 bg-white/10 rounded-full overflow-hidden">
                                        <div class="h-full bg-[#D4AF37] rounded-full transition-all"
                                            style="width: <?php echo $bar_pct; ?>%"></div>
                                    </div>
                                </div>
                                <div class="text-right shrink-0">
                                    <span
                                        class="text-white font-heading font-black text-lg"><?php echo $org_item['submission_count']; ?></span>
                                    <p class="text-white/40 text-[9px] uppercase tracking-wider">submissions</p>
                                </div>
                            </a>
                        <?php endforeach; ?>
                    </div>
                <?php endif; ?>
            </div>

            <!-- A6: Recent & Trending Section -->
            <div class="mb-10">
                <div class="flex flex-col sm:flex-row sm:items-end justify-between mb-4 gap-4">
                    <h3 class="text-white font-heading font-black text-xl">Recent & Trending</h3>
                    <!-- Tabs -->
                    <div class="flex bg-white/10 backdrop-blur rounded-lg p-1 border border-white/10 w-full sm:w-auto">
                        <button onclick="toggleTrendTab('recent')" id="btn-trend-recent"
                            class="flex-1 sm:flex-none text-xs font-bold px-4 py-2 rounded-md bg-white text-[#001F3F] transition-all">Latest
                            Uploads</button>
                        <button onclick="toggleTrendTab('rated')" id="btn-trend-rated"
                            class="flex-1 sm:flex-none text-xs font-bold px-4 py-2 rounded-md text-white/70 hover:text-white transition-all">Top
                            Rated</button>
                    </div>
                </div>

                <!-- Recent Container -->
                <div id="trend-recent" class="grid grid-cols-1 sm:grid-cols-2 lg:grid-cols-5 gap-4">
                    <?php if (empty($recent_subs)): ?>
                        <div
                            class="col-span-full text-white/50 text-center py-6 bg-white/5 rounded-xl border border-white/10 text-sm">
                            No recent submissions found.</div>
                    <?php else: ?>
                        <?php foreach ($recent_subs as $r_sub): ?>
                            <a href="view_submission.php?id=<?php echo $r_sub['id']; ?>"
                                class="group bg-white/10 backdrop-blur rounded-xl border border-white/10 p-4 hover:bg-white/20 transition-all flex flex-col h-full">
                                <div class="text-[10px] font-bold text-[#D4AF37] uppercase tracking-wider mb-2">
                                    <?php echo date('M j, Y', strtotime($r_sub['created_at'])); ?>
                                </div>
                                <h4
                                    class="text-white font-bold text-sm leading-snug mb-2 group-hover:text-blue-200 transition-colors line-clamp-3">
                                    <?php echo htmlspecialchars($r_sub['title']); ?>
                                </h4>
                                <div class="mt-auto pt-3 border-t border-white/10">
                                    <div class="text-[10px] text-white/60 truncate"
                                        title="<?php echo htmlspecialchars($r_sub['org_name']); ?>"><i
                                            class="fa-regular fa-building mr-1"></i><?php echo htmlspecialchars($r_sub['org_name']); ?>
                                    </div>
                                </div>
                            </a>
                        <?php endforeach; ?>
                    <?php endif; ?>
                </div>

                <!-- Top Rated Container -->
                <div id="trend-rated" class="hidden grid grid-cols-1 sm:grid-cols-2 lg:grid-cols-5 gap-4">
                    <?php if (empty($top_rated_subs)): ?>
                        <div
                            class="col-span-full text-white/50 text-center py-6 bg-white/5 rounded-xl border border-white/10 text-sm">
                            No rated submissions found.</div>
                    <?php else: ?>
                        <?php foreach ($top_rated_subs as $t_sub): ?>
                            <a href="view_submission.php?id=<?php echo $t_sub['id']; ?>"
                                class="group bg-white/10 backdrop-blur rounded-xl border border-white/10 p-4 hover:bg-white/20 transition-all flex flex-col h-full">
                                <div
                                    class="flex items-center gap-1.5 mb-2 bg-white/10 w-fit px-2 py-1 rounded text-[#D4AF37] text-xs font-bold">
                                    <i class="fa-solid fa-star"></i> <?php echo number_format($t_sub['avg_rating'], 1); ?>
                                </div>
                                <h4
                                    class="text-white font-bold text-sm leading-snug mb-2 group-hover:text-blue-200 transition-colors line-clamp-3">
                                    <?php echo htmlspecialchars($t_sub['title']); ?>
                                </h4>
                                <div class="mt-auto pt-3 border-t border-white/10">
                                    <div class="text-[10px] text-white/60 truncate"
                                        title="<?php echo htmlspecialchars($t_sub['org_name']); ?>"><i
                                            class="fa-regular fa-building mr-1"></i><?php echo htmlspecialchars($t_sub['org_name']); ?>
                                    </div>
                                </div>
                            </a>
                        <?php endforeach; ?>
                    <?php endif; ?>
                </div>
            </div>

            <script>
                function toggleTrendTab(tab) {
                    if (tab === 'recent') {
                        document.getElementById('trend-recent').classList.remove('hidden');
                        document.getElementById('trend-rated').classList.add('hidden');
                        document.getElementById('btn-trend-recent').className = 'flex-1 sm:flex-none text-xs font-bold px-4 py-2 rounded-md bg-white text-[#001F3F] transition-all';
                        document.getElementById('btn-trend-rated').className = 'flex-1 sm:flex-none text-xs font-bold px-4 py-2 rounded-md text-white/70 hover:text-white transition-all';
                    } else {
                        document.getElementById('trend-rated').classList.remove('hidden');
                        document.getElementById('trend-recent').classList.add('hidden');
                        document.getElementById('btn-trend-rated').className = 'flex-1 sm:flex-none text-xs font-bold px-4 py-2 rounded-md bg-white text-[#001F3F] transition-all';
                        document.getElementById('btn-trend-recent').className = 'flex-1 sm:flex-none text-xs font-bold px-4 py-2 rounded-md text-white/70 hover:text-white transition-all';
                    }
                }
            </script>

            <!-- Row 3: Sector + Rating + Category -->
            <div class="grid grid-cols-1 lg:grid-cols-3 gap-6">
                <div class="bg-white/10 backdrop-blur rounded-xl p-5 border border-white/10">
                    <h3 class="text-white font-bold text-sm mb-4"><i
                            class="fa-solid fa-building mr-2 text-[#D4AF37]"></i>Sector Distribution</h3>
                    <canvas id="sectorChart" height="220"></canvas>
                </div>
                <div class="bg-white/10 backdrop-blur rounded-xl p-5 border border-white/10">
                    <h3 class="text-white font-bold text-sm mb-4"><i
                            class="fa-solid fa-star mr-2 text-[#D4AF37]"></i>Rating Breakdown</h3>
                    <canvas id="ratingChart" height="220"></canvas>
                </div>
                <div class="bg-white/10 backdrop-blur rounded-xl p-5 border border-white/10">
                    <h3 class="text-white font-bold text-sm mb-4"><i class="fa-solid fa-tags mr-2 text-[#D4AF37]"></i>By
                        Category</h3>
                    <canvas id="categoryChart" height="220"></canvas>
                </div>
            </div>

            <!-- A7: Smart Filters Panel -->
            <div class="mt-8 bg-white/10 backdrop-blur-md border border-white/20 rounded-2xl overflow-hidden">
                <div class="p-4 px-6 flex items-center justify-between cursor-pointer hover:bg-white/5 transition-colors"
                    onclick="document.getElementById('smart-filters-content').classList.toggle('hidden'); document.getElementById('smart-filters-icon').classList.toggle('rotate-180');">
                    <div class="flex items-center gap-3">
                        <div
                            class="w-10 h-10 rounded-full bg-blue-500/20 text-blue-300 flex items-center justify-center">
                            <i class="fa-solid fa-filter"></i>
                        </div>
                        <div>
                            <h3 class="text-white font-bold text-lg">Smart Filters</h3>
                            <p class="text-blue-200 text-xs">Refine the dashboard data to your specific needs</p>
                        </div>
                    </div>
                    <div>
                        <i id="smart-filters-icon"
                            class="fa-solid fa-chevron-down text-white/50 transition-transform duration-300 <?php echo ($f_county || $f_sector || $f_search) ? 'rotate-180' : ''; ?>"></i>
                    </div>
                </div>

                <div id="smart-filters-content"
                    class="<?php echo ($f_county || $f_sector || $f_search) ? '' : 'hidden'; ?>">
                    <div class="p-6 pt-0 border-t border-white/10 mt-2">
                        <form method="GET" action="submitted.php"
                            class="grid grid-cols-1 md:grid-cols-4 gap-4 items-end">
                            <div>
                                <label class="block text-blue-200 text-xs font-bold mb-2 tracking-wide uppercase">Search
                                    Keyword</label>
                                <div class="relative">
                                    <div class="absolute inset-y-0 left-0 pl-3 flex items-center pointer-events-none">
                                        <i class="fa-solid fa-search text-white/40"></i>
                                    </div>
                                    <input type="text" name="q" value="<?php echo htmlspecialchars($f_search); ?>"
                                        placeholder="Search submissions..."
                                        class="w-full bg-white/5 border border-white/20 rounded-lg py-2.5 pl-10 pr-4 text-white placeholder-white/40 focus:outline-none focus:border-blue-400 focus:ring-1 focus:ring-blue-400 transition-colors">
                                </div>
                            </div>

                            <div>
                                <label
                                    class="block text-blue-200 text-xs font-bold mb-2 tracking-wide uppercase">County</label>
                                <select name="county"
                                    class="w-full bg-[#1a2b4c] border border-white/20 rounded-lg py-2.5 px-4 text-white focus:outline-none focus:border-blue-400 focus:ring-1 focus:ring-blue-400 transition-colors appearance-none">
                                    <option value="">All Counties</option>
                                    <?php
                                    $all_counties = ["National", "Baringo", "Bomet", "Bungoma", "Busia", "Elgeyo-Marakwet", "Embu", "Garissa", "Homa Bay", "Isiolo", "Kajiado", "Kakamega", "Kericho", "Kiambu", "Kilifi", "Kirinyaga", "Kisii", "Kisumu", "Kitui", "Kwale", "Laikipia", "Lamu", "Machakos", "Makueni", "Mandera", "Marsabit", "Meru", "Migori", "Mombasa", "Murang'a", "Nairobi", "Nakuru", "Nandi", "Narok", "Nyamira", "Nyandarua", "Nyeri", "Samburu", "Siaya", "Taita-Taveta", "Tana River", "Tharaka-Nithi", "Trans Nzoia", "Turkana", "Uasin Gishu", "Vihiga", "Wajir", "West Pokot"];
                                    foreach ($all_counties as $c) {
                                        $selected = (strcasecmp($f_county, $c) === 0) ? 'selected' : '';
                                        echo "<option value=\"$c\" $selected>$c</option>";
                                    }
                                    ?>
                                </select>
                            </div>

                            <div>
                                <label class="block text-blue-200 text-xs font-bold mb-2 tracking-wide uppercase">Sector
                                    / Role</label>
                                <select name="sector"
                                    class="w-full bg-[#1a2b4c] border border-white/20 rounded-lg py-2.5 px-4 text-white focus:outline-none focus:border-blue-400 focus:ring-1 focus:ring-blue-400 transition-colors appearance-none">
                                    <option value="">All Sectors</option>
                                    <option value="State Actor" <?php echo ($f_sector == 'State Actor') ? 'selected' : ''; ?>>State Actor</option>
                                    <option value="Non-State Actor" <?php echo ($f_sector == 'Non-State Actor') ? 'selected' : ''; ?>>Non-State Actor</option>
                                    <option value="Private Sector" <?php echo ($f_sector == 'Private Sector') ? 'selected' : ''; ?>>Private Sector</option>
                                    <option value="Academia" <?php echo ($f_sector == 'Academia') ? 'selected' : ''; ?>>
                                        Academia</option>
                                    <option value="Media" <?php echo ($f_sector == 'Media') ? 'selected' : ''; ?>>Media
                                    </option>
                                    <option value="Citizen" <?php echo ($f_sector == 'Citizen') ? 'selected' : ''; ?>>
                                        Citizen</option>
                                </select>
                            </div>

                            <div class="flex gap-2">
                                <button type="submit"
                                    class="flex-1 bg-gradient-to-r from-blue-500 to-blue-600 hover:from-blue-600 hover:to-blue-700 text-white font-bold py-2.5 px-4 rounded-lg shadow-lg hover:shadow-xl transition-all flex justify-center items-center gap-2">
                                    <i class="fa-solid fa-check"></i> Apply
                                </button>
                                <?php if ($f_county || $f_sector || $f_search): ?>
                                    <a href="submitted.php"
                                        class="bg-white/10 hover:bg-white/20 text-white py-2.5 px-4 rounded-lg transition-colors flex justify-center items-center"
                                        title="Clear Filters">
                                        <i class="fa-solid fa-xmark"></i>
                                    </a>
                                <?php endif; ?>
                            </div>
                        </form>
                    </div>
                </div>
            </div>

        </div>
    </section>

    <!-- All Submissions Grid -->
    <div style="background:linear-gradient(180deg,#012040 0%,#011628 100%);">
        <main class="max-w-7xl mx-auto px-4 py-10" id="all-submissions">
            <?php
            // Build descriptive filter title
            $has_active_filter = ($f_subcategory || $f_search || $f_county || $f_sector || $f_rating);
            if ($has_active_filter):
                $filter_title = 'Filtered Results';
                $filter_icon = 'fa-filter';
                if ($f_subcategory) {
                    $filter_title = 'Sub-category: ' . htmlspecialchars($f_subcategory_name ?: $f_subcategory);
                    $filter_icon = 'fa-tag';
                } elseif ($f_search) {
                    $filter_title = 'Search: &ldquo;' . htmlspecialchars($f_search) . '&rdquo;';
                    $filter_icon = 'fa-search';
                } elseif ($f_county) {
                    $filter_title = 'County: ' . htmlspecialchars($f_county);
                    $filter_icon = 'fa-location-dot';
                } elseif ($f_sector) {
                    $filter_title = 'Sector: ' . htmlspecialchars($f_sector);
                    $filter_icon = 'fa-building';
                } elseif ($f_rating) {
                    $filter_title = 'Rating: ' . htmlspecialchars($f_rating) . ' Star(s)';
                    $filter_icon = 'fa-star';
                }
                ?>
                <div class="rounded-xl p-4 mb-6 flex flex-col sm:flex-row items-start sm:items-center justify-between gap-3"
                    style="background:rgba(0,200,180,0.12);border:1px solid rgba(0,200,180,0.25);">
                    <div class="flex items-center gap-3">
                        <div class="w-10 h-10 rounded-full flex items-center justify-center shrink-0 text-teal-300"
                            style="background:rgba(0,200,180,0.2);">
                            <i class="fa-solid <?php echo $filter_icon; ?>"></i>
                        </div>
                        <div>
                            <p class="font-bold text-white"><?php echo $filter_title; ?></p>
                            <p class="text-teal-300 text-sm"><?php echo number_format(count($all_submissions)); ?>
                                submissions found</p>
                        </div>
                    </div>
                    <a href="submitted.php#top"
                        class="inline-flex items-center gap-1 text-sm font-bold text-teal-300 hover:text-white px-3 py-1.5 rounded-lg transition-colors shrink-0"
                        style="background:rgba(0,180,160,0.2);hover:background:rgba(0,180,160,0.35);"><i
                            class="fa-solid fa-xmark"></i> Clear Filter</a>
                </div>
            <?php endif; ?>

            <div class="flex flex-col sm:flex-row items-start sm:items-center justify-between mb-6 gap-4">
                <div>
                    <h2 class="font-heading font-bold text-xl text-white">Submitted Data</h2>
                    <p class="text-xs text-blue-200/60"><?php echo number_format(count($all_submissions)); ?> verified
                        submissions found.</p>
                </div>
            </div>

            <?php if (empty($all_submissions)): ?>
                <div class="rounded-2xl p-16 text-center"
                    style="background:rgba(255,255,255,0.05);border:1px solid rgba(255,255,255,0.1);">
                    <div class="w-14 h-14 mx-auto rounded-full flex items-center justify-center text-2xl mb-4"
                        style="background:rgba(255,255,255,0.08);color:rgba(255,255,255,0.3);">
                        <i class="fa-solid fa-inbox"></i>
                    </div>
                    <h3 class="text-lg font-bold text-white/80 mb-2">No Submissions Found</h3>
                    <p class="text-blue-200/50 text-sm max-w-sm mx-auto">Try adjusting or removing the current filters to
                        see more results.</p>
                </div>
            <?php else: ?>
                <div class="grid grid-cols-1 sm:grid-cols-2 lg:grid-cols-3 xl:grid-cols-4 gap-5">
                    <?php foreach ($all_submissions as $sub): ?>
                        <?php include 'partials/card.php'; ?>
                    <?php endforeach; ?>
                </div>
            <?php endif; ?>
        </main>
    </div><!-- end dark results wrapper -->

    <!-- Footer -->
    <?php include 'footer.php'; ?>

    <!-- Dashboard Configuration -->
    <script>
        window.SaniChartConfig = {
            hash: "submissions",
            trend: {
                id: 'trendChart',
                labels: <?php echo json_encode($trend_labels); ?>,
                values: <?php echo json_encode($trend_values); ?>
            },
            sector: {
                id: 'sectorChart',
                labels: <?php echo json_encode(array_values(array_keys($sector_counts_dash))); ?>,
                values: <?php echo json_encode(array_values($sector_counts_dash)); ?>
            },
            rating: {
                id: 'ratingChart',
                values: <?php echo json_encode($rating_values); ?>
            },
            category: {
                id: 'categoryChart',
                labels: <?php
                $cat_labels_top = array_slice(array_keys($cat_dist), 0, 10);
                $cat_values_top = array_slice(array_values($cat_dist), 0, 10);
                $cat_label_names = [];
                foreach ($cat_labels_top as $cl) {
                    $cat_label_names[] = is_numeric($cl) ? ($cat_map[$cl] ?? "Cat #$cl") : $cl;
                }
                echo json_encode($cat_label_names);
                ?>,
                values: <?php echo json_encode($cat_values_top); ?>,
                ids: <?php echo json_encode($cat_labels_top); ?>
            }
        };
    </script>

    <!-- Hero Search Script -->
    <script>
        const searchInput = document.getElementById('heroSearch');
        const searchResults = document.getElementById('searchResults');
        const searchSpinner = document.getElementById('searchSpinner');
        let searchTimeout;

        searchInput.addEventListener('input', function (e) {
            clearTimeout(searchTimeout);
            const q = e.target.value.trim();

            if (q.length < 2) {
                searchResults.classList.add('hidden');
                searchResults.innerHTML = '';
                searchSpinner.classList.add('hidden');
                return;
            }

            searchSpinner.classList.remove('hidden');

            searchTimeout = setTimeout(() => {
                fetch(`api/search.php?q=${encodeURIComponent(q)}`)
                    .then(res => res.json())
                    .then(data => {
                        searchSpinner.classList.add('hidden');
                        renderSearchResults(data, q);
                    })
                    .catch(err => {
                        console.error('Search error:', err);
                        searchSpinner.classList.add('hidden');
                    });
            }, 400); // 400ms debounce
        });

        function renderSearchResults(data, q) {
            const hasSubs = data.submissions && data.submissions.length > 0;
            const hasOrgs = data.organisations && data.organisations.length > 0;
            const hasCounties = data.counties && data.counties.length > 0;

            if (!hasSubs && !hasOrgs && !hasCounties) {
                searchResults.innerHTML = `
                    <div class="p-4 text-center text-slate-500">
                        <i class="fa-solid fa-ghost text-2xl mb-2 text-slate-300"></i>
                        <p>No results found for "<span class="font-bold text-slate-700">${escapeHtml(q)}</span>"</p>
                        <p class="text-xs mt-1">Try searching for a technology, county, or organisation name.</p>
                    </div>`;
                searchResults.classList.remove('hidden');
                return;
            }

            let html = '';

            // 1. County Matches (High intent)
            if (hasCounties) {
                html += `<div class="bg-slate-50 px-4 py-2 border-b border-slate-100"><h4 class="text-xs font-bold text-slate-500 uppercase tracking-wider"><i class="fa-solid fa-map text-slate-400 mr-2"></i>Counties</h4></div>`;
                data.counties.forEach(c => {
                    html += `
                        <a href="submitted.php?county=${encodeURIComponent(c.name)}" class="block px-4 py-3 hover:bg-slate-50 transition-colors flex items-center justify-between group">
                            <span class="font-bold text-slate-900 group-hover:text-teal-600 transition-colors">${c.name}</span>
                            <span class="text-xs bg-slate-100 text-slate-600 font-medium px-2 py-1 rounded-md">${c.count} submission${c.count !== 1 ? 's' : ''}</span>
                        </a>`;
                });
            }

            // 2. Organisation Matches
            if (hasOrgs) {
                html += `<div class="bg-slate-50 px-4 py-2 border-b border-slate-100"><h4 class="text-xs font-bold text-slate-500 uppercase tracking-wider"><i class="fa-solid fa-building text-slate-400 mr-2"></i>Organisations</h4></div>`;
                data.organisations.forEach(o => {
                    html += `
                        <a href="explore.php?q=${encodeURIComponent(o.name)}" class="block px-4 py-3 hover:bg-slate-50 transition-colors flex flex-col sm:flex-row sm:items-center justify-between group gap-1">
                            <span class="font-bold text-slate-900 group-hover:text-indigo-600 transition-colors">${o.name}</span>
                            <div class="flex items-center gap-2 text-xs text-slate-500">
                                ${o.county ? `<span><i class="fa-solid fa-location-dot text-slate-300 mr-1"></i>${o.county}</span>` : ''}
                                ${o.role ? `<span class="bg-slate-100 px-2 py-0.5 rounded">${o.role}</span>` : ''}
                            </div>
                        </a>`;
                });
            }

            // 3. Submission Matches
            if (hasSubs) {
                html += `<div class="bg-slate-50 px-4 py-2 border-b border-slate-100"><h4 class="text-xs font-bold text-slate-500 uppercase tracking-wider"><i class="fa-solid fa-file-lines text-slate-400 mr-2"></i>Submissions</h4></div>`;
                data.submissions.forEach(s => {
                    html += `
                        <a href="view_submission.php?id=${s.id}" class="block px-4 py-3 hover:bg-slate-50 transition-colors group">
                            <div class="font-bold text-slate-900 group-hover:text-teal-600 transition-colors line-clamp-1">${s.title}</div>
                            <div class="flex items-center gap-3 text-xs text-slate-500 mt-1">
                                ${s.org_name ? `<span><i class="fa-solid fa-building text-slate-300 mr-1"></i>${s.org_name}</span>` : ''}
                                ${s.county ? `<span><i class="fa-solid fa-location-dot text-slate-300 mr-1"></i>${s.county}</span>` : ''}
                                <span><i class="fa-regular fa-calendar text-slate-300 mr-1"></i>${s.date}</span>
                            </div>
                        </a>`;
                });
            }

            searchResults.innerHTML = html;
            searchResults.classList.remove('hidden');
        }

        // Close dropdown when clicking outside
        document.addEventListener('click', function (e) {
            if (!searchInput.contains(e.target) && !searchResults.contains(e.target)) {
                searchResults.classList.add('hidden');
            }
        });

        // Basic HTML escaper
        function escapeHtml(unsafe) {
            return unsafe
                .replace(/&/g, "&amp;")
                .replace(/</g, "&lt;")
                .replace(/>/g, "&gt;")
                .replace(/"/g, "&quot;")
                .replace(/'/g, "&#039;");
        }
    </script>
    <!-- Interactive Map Configuration -->
    <script>
        window.SaniMapConfig = {
            containerId: "kenyaMap",
            countyData: <?php echo json_encode($county_counts_dash); ?>,
            hash: "submissions"
        };
    </script>

    <script src="assets/js/dashboard-charts.js"></script>
    <script src="assets/js/filter-scroll.js"></script>
</body>

</html>