<?php

function getSetting($key, $default = '') {
    $db = getDB();
    $stmt = $db->prepare("SELECT setting_value FROM site_settings WHERE setting_key = ?");
    $stmt->execute([$key]);
    $row = $stmt->fetch();
    return $row ? $row['setting_value'] : $default;
}

function getAllSettings() {
    $db = getDB();
    $stmt = $db->query("SELECT setting_key, setting_value FROM site_settings");
    $settings = [];
    while ($row = $stmt->fetch()) {
        $settings[$row['setting_key']] = $row['setting_value'];
    }
    return $settings;
}

function getActiveSliders() {
    $db = getDB();
    return $db->query("SELECT * FROM sliders WHERE active = TRUE ORDER BY sort_order ASC")->fetchAll();
}

function getActiveSections() {
    $db = getDB();
    return $db->query("SELECT * FROM sections WHERE active = TRUE ORDER BY sort_order ASC")->fetchAll();
}

function getActiveAnnouncements() {
    $db = getDB();
    return $db->query("SELECT * FROM announcements WHERE active = TRUE ORDER BY sort_order ASC")->fetchAll();
}

function getActiveResources() {
    $db = getDB();
    return $db->query("SELECT * FROM resources WHERE active = TRUE ORDER BY sort_order ASC")->fetchAll();
}

function getActiveFacilities() {
    $db = getDB();
    return $db->query("SELECT * FROM facilities WHERE active = TRUE ORDER BY sort_order ASC")->fetchAll();
}

function getActiveServices() {
    $db = getDB();
    return $db->query("SELECT * FROM services WHERE active = TRUE ORDER BY sort_order ASC")->fetchAll();
}

function getActiveWorkshops() {
    $db = getDB();
    return $db->query("SELECT * FROM workshops WHERE active = TRUE ORDER BY sort_order ASC")->fetchAll();
}

function getActiveTeamMembers() {
    $db = getDB();
    return $db->query("SELECT * FROM team_members WHERE active = TRUE ORDER BY sort_order ASC")->fetchAll();
}

function getActiveAlumni() {
    $db = getDB();
    return $db->query("SELECT * FROM alumni_team WHERE active = TRUE ORDER BY sort_order ASC")->fetchAll();
}

function getArticleCategories() {
    $db = getDB();
    return $db->query("SELECT * FROM article_categories ORDER BY sort_order ASC")->fetchAll();
}

function getPublishedArticles($limit = null, $offset = 0, $categoryId = null, $search = null) {
    $db = getDB();
    $sql = "SELECT a.*, c.name as category_name, c.slug as category_slug FROM articles a LEFT JOIN article_categories c ON a.category_id = c.id WHERE a.published = TRUE";
    $params = [];
    if ($categoryId) {
        $sql .= " AND a.category_id = ?";
        $params[] = $categoryId;
    }
    if ($search) {
        $sql .= " AND (a.title ILIKE ? OR a.content ILIKE ?)";
        $params[] = "%$search%";
        $params[] = "%$search%";
    }
    $sql .= " ORDER BY a.created_at DESC";
    if ($limit) {
        $sql .= " LIMIT " . intval($limit) . " OFFSET " . intval($offset);
    }
    $stmt = $db->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchAll();
}

function countPublishedArticles($categoryId = null, $search = null) {
    $db = getDB();
    $sql = "SELECT COUNT(*) FROM articles WHERE published = TRUE";
    $params = [];
    if ($categoryId) {
        $sql .= " AND category_id = ?";
        $params[] = $categoryId;
    }
    if ($search) {
        $sql .= " AND (title ILIKE ? OR content ILIKE ?)";
        $params[] = "%$search%";
        $params[] = "%$search%";
    }
    $stmt = $db->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchColumn();
}

function getYoutubeEmbedUrl($url) {
    $pattern = '/(?:youtube\.com\/(?:watch\?v=|embed\/)|youtu\.be\/)([a-zA-Z0-9_-]+)/';
    if (preg_match($pattern, $url, $matches)) {
        return 'https://www.youtube.com/embed/' . $matches[1];
    }
    return $url;
}

function getMenuItems() {
    $db = getDB();
    $items = $db->query("SELECT * FROM menu_items WHERE active = TRUE ORDER BY sort_order ASC")->fetchAll();
    $menu = [];
    $children = [];
    foreach ($items as $item) {
        if ($item['parent_id']) {
            $children[$item['parent_id']][] = $item;
        } else {
            $menu[] = $item;
        }
    }
    foreach ($menu as &$item) {
        $item['children'] = $children[$item['id']] ?? [];
    }
    return $menu;
}

function getAllAdminUsers() {
    $db = getDB();
    return $db->query("SELECT * FROM admin_users ORDER BY created_at DESC")->fetchAll();
}

function getAllRoles() {
    $db = getDB();
    return $db->query("SELECT * FROM admin_roles ORDER BY id ASC")->fetchAll();
}

function getSeoMeta($path) {
    $db = getDB();
    $stmt = $db->prepare("SELECT * FROM seo_metadata WHERE page_path = ?");
    $stmt->execute([$path]);
    return $stmt->fetch();
}

function getGalleryCategories() {
    $db = getDB();
    return $db->query("SELECT gc.*, COUNT(gi.id) as image_count FROM gallery_categories gc LEFT JOIN gallery_images gi ON gc.id = gi.category_id AND gi.active = TRUE WHERE gc.active = TRUE GROUP BY gc.id ORDER BY gc.sort_order ASC")->fetchAll();
}

function getGalleryCategoryBySlug($slug) {
    $db = getDB();
    $stmt = $db->prepare("SELECT * FROM gallery_categories WHERE slug = ? AND active = TRUE");
    $stmt->execute([$slug]);
    return $stmt->fetch();
}

function getGalleryImages($categoryId = null) {
    $db = getDB();
    if ($categoryId) {
        $stmt = $db->prepare("SELECT gi.*, gc.name as category_name, gc.slug as category_slug FROM gallery_images gi LEFT JOIN gallery_categories gc ON gi.category_id = gc.id WHERE gi.active = TRUE AND gi.category_id = ? ORDER BY gi.created_at DESC");
        $stmt->execute([$categoryId]);
        return $stmt->fetchAll();
    }
    return $db->query("SELECT gi.*, gc.name as category_name, gc.slug as category_slug FROM gallery_images gi LEFT JOIN gallery_categories gc ON gi.category_id = gc.id WHERE gi.active = TRUE ORDER BY gi.created_at DESC")->fetchAll();
}

function getRecentGalleryImages($limit = 12) {
    $db = getDB();
    $stmt = $db->prepare("SELECT gi.*, gc.name as category_name, gc.slug as category_slug FROM gallery_images gi LEFT JOIN gallery_categories gc ON gi.category_id = gc.id WHERE gi.active = TRUE ORDER BY gi.created_at DESC LIMIT ?");
    $stmt->execute([$limit]);
    return $stmt->fetchAll();
}
