<?php
// Utility functions

function generateSlug($string) {
    return strtolower(trim(preg_replace('/[^A-Za-z0-9-]+/', '-', $string)));
}

function truncateText($text, $length = 150) {
    if (strlen($text) > $length) {
        $text = substr($text, 0, $length);
        $text = substr($text, 0, strrpos($text, ' ')) . '...';
    }
    return $text;
}

function formatDate($date) {
    return date('d-M-Y', strtotime($date));
}

function getCategories() {
    $pdo = getDB();
    $stmt = $pdo->query("SELECT * FROM categories ORDER BY name");
    return $stmt->fetchAll();
}

function getUsers() {
    $pdo = getDB();
    $stmt = $pdo->query("SELECT id, username, email, role FROM users ORDER BY username");
    return $stmt->fetchAll();
}

function getCategoryById($id) {
    $pdo = getDB();
    $stmt = $pdo->prepare("SELECT * FROM categories WHERE id = ?");
    $stmt->execute([$id]);
    return $stmt->fetch();
}

function getCategoryBySlug($slug) {
    $pdo = getDB();
    $stmt = $pdo->prepare("SELECT * FROM categories WHERE slug = ?");
    $stmt->execute([$slug]);
    return $stmt->fetch();
}

function getPostsByCategory($category_slug, $limit = null) {
    $pdo = getDB();
    $sql = "SELECT p.*, c.name as category_name, c.slug as category_slug,
                   COALESCE(p.manual_author_name, u.username) as author_name
            FROM posts p
            LEFT JOIN categories c ON p.category_id = c.id
            LEFT JOIN users u ON p.author_id = u.id
            WHERE c.slug = ? AND p.status = 'published'
            ORDER BY p.created_at DESC";

    if ($limit) {
        $sql .= " LIMIT " . intval($limit);
    }

    $stmt = $pdo->prepare($sql);
    $stmt->execute([$category_slug]);
    return $stmt->fetchAll();
}

function getAllPublishedPosts($limit = null, $search = null) {
    $pdo = getDB();
    $sql = "SELECT p.*, c.name as category_name, c.slug as category_slug,
                   COALESCE(p.manual_author_name, u.username) as author_name
            FROM posts p
            LEFT JOIN categories c ON p.category_id = c.id
            LEFT JOIN users u ON p.author_id = u.id
            WHERE p.status = 'published'";

    $params = [];
    if ($search) {
        $searchTerms = explode(' ', $search);
        $conditions = [];
        foreach ($searchTerms as $term) {
            $term = trim($term);
            if (!empty($term)) {
                $conditions[] = "(p.title LIKE ? OR p.content LIKE ? OR p.excerpt LIKE ?)";
                $params[] = '%' . $term . '%';
                $params[] = '%' . $term . '%';
                $params[] = '%' . $term . '%';
            }
        }
        if (!empty($conditions)) {
            $sql .= " AND (" . implode(' OR ', $conditions) . ")";
        }
    }

    $sql .= " ORDER BY p.created_at DESC";

    if ($limit) {
        $sql .= " LIMIT " . intval($limit);
    }

    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchAll();
}

function getPostBySlug($slug) {
    $pdo = getDB();
    $stmt = $pdo->prepare("SELECT p.*, c.name as category_name, c.slug as category_slug,
                                  COALESCE(p.manual_author_name, u.username) as author_name
                          FROM posts p
                          LEFT JOIN categories c ON p.category_id = c.id
                          LEFT JOIN users u ON p.author_id = u.id
                          WHERE p.slug = ? AND p.status = 'published'");
    $stmt->execute([$slug]);
    return $stmt->fetch();
}

function getSiteSetting($key) {
    $pdo = getDB();
    $stmt = $pdo->prepare("SELECT setting_value FROM site_settings WHERE setting_key = ? LIMIT 1");
    $stmt->execute([$key]);
    $result = $stmt->fetch();
    return $result ? $result['setting_value'] : null;
}

function updateSiteSetting($key, $value) {
    $pdo = getDB();
    $stmt = $pdo->prepare("INSERT INTO site_settings (setting_key, setting_value) VALUES (?, ?) ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value)");
    return $stmt->execute([$key, $value]);
}

function getActiveAnnouncements() {
    $pdo = getDB();
    $stmt = $pdo->query("SELECT * FROM announcements WHERE status = 'active' AND show_on_home = 1 ORDER BY created_at DESC");
    return $stmt->fetchAll();
}
?>
