<?php
 
 /**
 * =============================================================================
 * @package         Blue Raven (Free Version)
 * @Plugin URI      https://enterrahost.com/plugins/blue-raven
 * 
 * @Author          EnterraHost
 * @Author URI      https://enterrahost.com
 * @License         GPL-2.0-or-later
 *  
 * @Copyright       (c) 2025 EnterraHost
 * @Contents        Product Load for the Product table
 *
 * =============================================================================
 * 
 * || INDEX:
 * ||   - LOAD PRODUCTS
 * || 
 * =============================================================================
 */

if ( ! defined( 'ABSPATH' ) ) exit; 

/*
============================================================
    LOAD PRODUCTS
============================================================
*/

function BlueRaven_fetchProducts($filter, $search, $page){
    
    global $wpdb;
    
	$products_table_name = $wpdb->prefix . 'BlueRavenProducts';
	$postmeta_table_name = $wpdb->prefix . 'postmeta';
	$per_page = 10;
	$offset   = ($page - 1) * $per_page;
    
    ## =========== Initialize WHERE clauses array =========== ##
    
    $where_clauses = array();
    $query_params = array();
        
    ## =========== Add filter conditions if provided =========== ##
    
	$filter = stripslashes($filter);
	$filterData = json_decode($filter, true);

	$category = $filterData['category'] ?? '';
	$status   = $filterData['status'] ?? '';
	$stock    = $filterData['stock'] ?? '';
	$favorite = $filterData['favorite'] ?? '';

	$stock_int    = filter_var($stock, FILTER_VALIDATE_BOOLEAN) ? 1 : 0;
	$favorite_int = filter_var($favorite, FILTER_VALIDATE_BOOLEAN) ? 1 : 0;
    
	if (!empty($filter)) {
		if (!empty($status)) {
			$where_clauses[] = 'pt.status = %s';
			$query_params[] = $status;
		}
		if (!empty($category)) {
			$where_clauses[] = 'pt.category = %s';
			$query_params[] = $category;
		}
		if ($favorite_int === 1) {
			$where_clauses[] = 'pt.favorite = %d';
			$query_params[] = 1;
		}
	}

	$stock_join_sql = '';
	if ($stock_int === 1) {
		$stock_join_sql = "INNER JOIN `{$postmeta_table_name}` AS pm ON pt.woo_product_id = pm.post_id AND pm.meta_key = '_stock_status' AND pm.meta_value = 'instock'";
	}

    ## =========== Add search condition if provided =========== ##
    
	if (!empty($search)) {
		$where_clauses[] = '(pt.title LIKE %s OR pt.description LIKE %s OR pt.sku LIKE %s)';
		$search_term = '%' . $wpdb->esc_like($search) . '%';
		$query_params[] = $search_term;
		$query_params[] = $search_term;
		$query_params[] = $search_term;
	}
        
    ## =========== Build the WHERE clause =========== ##

	$where_sql = !empty($where_clauses) ? 'WHERE ' . implode(' AND ', $where_clauses) : '';
    
    ## =========== Prepare and run the query =========== ##
    
    $query = $wpdb->prepare(
    	"SELECT pt.* FROM `{$products_table_name}` AS pt
    	{$stock_join_sql}
    	{$where_sql}
    	ORDER BY pt.created_at DESC
    	LIMIT %d OFFSET %d",
    	array_merge($query_params, array($per_page, $offset))
    );
    
    // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- query is safely prepared above
    $products = $wpdb->get_results($query, ARRAY_A);
    
    ## =========== Get total count for pagination =========== ##
     
    $count_query = $wpdb->prepare(
        "SELECT COUNT(*) FROM `{$products_table_name}` AS pt
        {$stock_join_sql}
        {$where_sql}",
        $query_params
    );
    
    // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- query is safely prepared above
    $total_items = (int) $wpdb->get_var($count_query);
    
    $total_pages = ceil($total_items / $per_page);
    
    return [$products, $total_items, $total_pages];

}
