<?php

function items_category($conn_rjllearning_specific, $conn_rjllearning, $institution_id, $search_string, $from_date, $to_date, $date_created, $limit, $offset)
{
    $sql_count = "SELECT  count(*) as total_rows FROM  items_category where institution_id =? AND status =?";
    $array_params = [$institution_id, 1];

    if ($search_string != null) {
        $sql_count .= " AND (category_name LIKE ?)";
        $array_params[] = '%' . $search_string . '%';
    }

    if ($date_created != null) {
        $sql_count .= " AND date_created = ?";
        $array_params[] = $date_created;
    }

    // Add date range condition
    if ($from_date != null && $to_date != null) {
        $sql_count .= " AND date_created BETWEEN ? AND ?";
        $array_params[] = $from_date;
        $array_params[] = $to_date;
    }

    $stmt = $conn_rjllearning_specific->prepare($sql_count);
    $stmt->execute($array_params);
    $result_total = $stmt->fetchAll();
    $totalrows = $result_total[0]['total_rows'];

    if ($limit == 'all') {

        $sql_item_category = "SELECT * FROM  items_category where institution_id =? AND status =?";
        $array_params = [$institution_id, 1];

        if ($search_string != null) {
            $sql_item_category .= " AND (category_name LIKE ?)";
            $array_params[] = '%' . $search_string . '%';
        }

        if ($date_created != null) {
            $sql_item_category .= " AND date_created = ?";
            $array_params[] = $date_created;
        }

        // Add date range condition
        if ($from_date != null && $to_date != null) {
            $sql_item_category .= " AND date_created BETWEEN ? AND ?";
            $array_params[] = $from_date;
            $array_params[] = $to_date;
        }

        $sql_item_category .=  "order by date_created desc, time_created desc";
    } else {
        $sql_item_category = "SELECT * FROM  items_category where institution_id =? AND status =?";
        $array_params = [$institution_id, 1];

        if ($search_string != null) {
            $sql_item_category .= " AND (category_name LIKE ?)";
            $array_params[] = '%' . $search_string . '%';
        }

        if ($date_created != null) {
            $sql_item_category .= " AND date_created = ?";
            $array_params[] = $date_created;
        }

        // Add date range condition
        if ($from_date != null && $to_date != null) {
            $sql_item_category .= " AND date_created BETWEEN ? AND ?";
            $array_params[] = $from_date;
            $array_params[] = $to_date;
        }
        $sql_item_category .= " order by date_created desc, time_created desc ";
        $sql_item_category .= "LIMIT " . $offset . " ," . $limit . "";
    }
    
    $stmt = $conn_rjllearning_specific->prepare($sql_item_category);
    $stmt->execute($array_params);
    $session_rows = $stmt->fetchAll();

    $array_return = [];

    foreach ($session_rows as $rows) {
    // Initialize under_details as null
    $under_details = null;

    // Fetching under category details if not null
    if (!empty($rows['under'])) {
        $sql_category = "SELECT id, category_name FROM items_category WHERE id = ? AND institution_id = ?";
        $stmtCategory = $conn_rjllearning_specific->prepare($sql_category);
        $stmtCategory->execute([$rows['under'], $rows['institution_id']]);
        $under_detail = $stmtCategory->fetch(PDO::FETCH_ASSOC); // Fetch as associative array

        // Ensure $under_detail is valid before assigning
        if ($under_detail && is_array($under_detail)) {
            $under_details = [
                "item_category_id" => $under_detail['id'],
                "category_name" => $under_detail['category_name']
            ];
        }
    }

    $array_return[] = [
        'category_id' => $rows['id'],
        'institution_id' => $rows['institution_id'],
        'category_name' => $rows['category_name'],
        'under' => $under_details, // Correctly formatted nested category details
        'date_created' => $rows['date_created'],
        'time_created' => $rows['time_created'],
        'status' => $rows['status'],
    ];
}


    if (count($session_rows) < 50) {

        return array('total_rows' => $totalrows, 'item_category_info' => $array_return, 'maxreach' => 1);
    } else {
        return array('total_rows' => $totalrows, 'item_category_info' => $array_return, 'maxreach' => 0);
    }
}




function get_specific_item_category($conn_rjllearning_specific, $institution_id, $category_id)
{
    try {
        // Query to fetch the specific category by ID
        $sql = "SELECT * FROM items_category WHERE institution_id = ? AND id = ? AND status = ?";
        $stmt = $conn_rjllearning_specific->prepare($sql);
        $stmt->execute([$institution_id, $category_id, 1]);
        $category = $stmt->fetch(PDO::FETCH_ASSOC);

        // Debugging: Check if category is retrieved
        if (!$category) {
            errorreturn("Category not found.");
            return; // Stop execution
        }

        // Debugging: Log fetched category
        // var_dump($category); die();

        // Initialize under_details as null
        $under_details = null;

        // Fetching under category details if 'under' is not null
        if (!empty($category['under'])) {
            // Debugging: Check 'under' value
            // var_dump($category['under']); die();

            $sql_category = "SELECT id, category_name FROM items_category WHERE id = ? AND institution_id = ?";
            $stmtCategory = $conn_rjllearning_specific->prepare($sql_category);
            $stmtCategory->execute([$category['under'], $institution_id]);
            $under_detail = $stmtCategory->fetch(PDO::FETCH_ASSOC);

            // Ensure valid under category details before assigning
            if ($under_detail) {
                $under_details = [
                    "item_category_id" => $under_detail['id'],
                    "category_name" => $under_detail['category_name']
                ];
            }
        }

        // Return category details
        $under_detail = ([
            'category_id' => $category['id'],
            'institution_id' => $category['institution_id'],
            'category_name' => $category['category_name'],
            'under' => $under_details,
            'date_created' => $category['date_created'],
            'time_created' => $category['time_created'],
            'status' => $category['status'],
        ]);
        return  $under_detail;
    } catch (Exception $e) {
        errorreturn("Error: " . $e->getMessage());
        return;
    }
}




function get_category_names($conn_rjllearning_specific, $institution_id) {
    try {
        // Query to fetch category names and IDs
        $sql = "SELECT id as category_id, category_name FROM items_category WHERE institution_id = ? AND status = ?";
        $stmt = $conn_rjllearning_specific->prepare($sql);
        $stmt->execute([$institution_id, 1]);
        $categories = $stmt->fetchAll(PDO::FETCH_ASSOC);

        // If no categories found, return an error
        if (!$categories) {
            errorreturn("No categories found for the given institution.");
        }

        // Return category names with IDs
        successreturn($categories);
    } catch (Exception $e) {
        errorreturn("Error: " . $e->getMessage());
    }
}
