<?php

// Prevent direct script access
if (basename(__FILE__) == basename($_SERVER['SCRIPT_FILENAME'])) {
    die('No direct script access allowed');
}

// --- Category Related Queries ---

/**
 * Creates a new category.
 */
function createCategory(string $name, ?int $parent_id = null): array {
    try {
        // Check for duplicate name at the same level
        $sql_check = "SELECT id FROM categories WHERE name = :name AND " .
                     ($parent_id === null ? "parent_id IS NULL" : "parent_id = :parent_id");
        $stmt_check = pdo()->prepare($sql_check);
        $stmt_check->bindValue(':name', $name);
        if ($parent_id !== null) {
            $stmt_check->bindValue(':parent_id', $parent_id, PDO::PARAM_INT);
        }
        $stmt_check->execute();
        if ($stmt_check->fetchColumn()) {
            return ['success' => false, 'message' => "دسته‌بندی با نام '{$name}' از قبل در این سطح وجود دارد."];
        }

        // Insert new category
        $stmt = pdo()->prepare("INSERT INTO categories (name, parent_id) VALUES (?, ?)");
        $result = $stmt->execute([$name, $parent_id]);
        if ($result) {
             return ['success' => true, 'message' => 'دسته‌بندی با موفقیت ایجاد شد.', 'id' => pdo()->lastInsertId()];
        } else {
             return ['success' => false, 'message' => 'خطا در افزودن دسته‌بندی به دیتابیس.'];
        }
    } catch (PDOException $e) {
        file_put_contents(ERROR_LOG_PATH, date('Y-m-d H:i:s') . " - DB Error creating category: " . $e->getMessage() . "\n", FILE_APPEND);
        return ['success' => false, 'message' => 'خطایی در پایگاه داده رخ داد.'];
    }
}

/**
 * Gets active categories, optionally filtered by parent ID.
 */
function getActiveCategories(?int $parent_id = null): array {
    try {
        if ($parent_id === null) {
            $stmt = pdo()->query("SELECT * FROM categories WHERE is_active = TRUE AND parent_id IS NULL ORDER BY name ASC");
        } else {
            $stmt = pdo()->prepare("SELECT * FROM categories WHERE is_active = TRUE AND parent_id = ? ORDER BY name ASC");
            $stmt->execute([$parent_id]);
        }
        return $stmt->fetchAll();
    } catch (PDOException $e) {
        file_put_contents(ERROR_LOG_PATH, date('Y-m-d H:i:s') . " - DB Error getting categories (parent: {$parent_id}): " . $e->getMessage() . "\n", FILE_APPEND);
        return [];
    }
}

/**
 * Gets a category by its ID.
 */
function getCategoryById(int $category_id): ?array {
    try {
        $stmt = pdo()->prepare("SELECT * FROM categories WHERE id = ?");
        $stmt->execute([$category_id]);
        return $stmt->fetch() ?: null;
    } catch (PDOException $e) {
        file_put_contents(ERROR_LOG_PATH, date('Y-m-d H:i:s') . " - DB Error getting category by ID {$category_id}: " . $e->getMessage() . "\n", FILE_APPEND);
        return null;
    }
}

/**
 * Gets the path (ancestors) of a category.
 */
function getCategoryPath(int $category_id): array {
    $path = [];
    $current_cat_id = $category_id;
    $safety_counter = 0; // Prevent infinite loops
    try {
        while ($current_cat_id !== null && $safety_counter < 10) {
            $category = getCategoryById($current_cat_id);
            if ($category) {
                array_unshift($path, $category);
                $current_cat_id = $category['parent_id'];
            } else {
                break; // Category not found
            }
            $safety_counter++;
        }
        if ($safety_counter >= 10) {
             file_put_contents(ERROR_LOG_PATH, date('Y-m-d H:i:s') . " - Warning: Potential infinite loop detected in getCategoryPath for category ID {$category_id}.\n", FILE_APPEND);
        }
    } catch (Exception $e) {
        // Catch potential errors from getCategoryById
        file_put_contents(ERROR_LOG_PATH, date('Y-m-d H:i:s') . " - Error getting category path for {$category_id}: " . $e->getMessage() . "\n", FILE_APPEND);
    }
    return $path;
}

// Add functions for updating, deleting (deactivating) categories if needed

?>
