Get the lowest price in last 30 days including CRON

Steps

<?php
// First create a table to hold the data (THIS IS FOR phphMyAdmin)
CREATE TABLE wp_lowest_price_in_30_days (
    id MEDIUMINT(9) NOT NULL AUTO_INCREMENT,
    product_id MEDIUMINT(9) NOT NULL,
    product_title VARCHAR(255) NOT NULL,
    regular_price FLOAT NOT NULL,
    sale_price FLOAT NOT NULL,
    date_of_entry DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    PRIMARY KEY (id)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
?>

<?php
// Schedule the morning CRON event to copy all pricing, if it doesn't already exist
if (!wp_next_scheduled('morning_cron_job')) {
    // Schedule the event for noon (12:00:00)
    wp_schedule_event(strtotime('12:00:00'), 'daily', 'morning_cron_job');
}
// Hook the function to the CRON event
add_action('morning_cron_job', 'run_morning_cron_job');
function run_morning_cron_job() {
    global $wpdb;

    $table_name = $wpdb->prefix . 'lowest_price_in_30_days';

    // Get all published WooCommerce products
    $products = wc_get_products(array(
        'status' => 'publish',
        'limit' => -1
    ));

    // Loop through products and insert data into custom table
    foreach ($products as $product) {
        $product_id = $product->get_id();
        $product_title = $product->get_title();
        $regular_price = $product->get_regular_price();
        $sale_price = $product->get_sale_price();
        
        $wpdb->insert(
            $table_name,
            array(
                'product_id' => $product_id,
                'product_title' => $product_title,
                'regular_price' => $regular_price,
                'sale_price' => $sale_price,
                'date_of_entry' => current_time('mysql')
            )
        );
    }
}

// Schedule the evening CRON event for table cleanup, if it doesn't already exist
if (!wp_next_scheduled('evening_cron_job')) {
    // Schedule the event for 21:00:00 (9 PM)
    wp_schedule_event(strtotime('21:00:00'), 'daily', 'evening_cron_job');
}
// Hook the function to the CRON event
add_action('evening_cron_job', 'run_evening_cron_job');

// Function to delete entries older than 30 days
function run_evening_cron_job() {
    global $wpdb;

    $table_name = $wpdb->prefix . 'lowest_price_in_30_days';
    $thirty_days_ago = date('Y-m-d 00:00:00', strtotime('-30 days'));

    // Prepare and execute the query to delete old entries
    $wpdb->query(
        $wpdb->prepare(
            "DELETE FROM $table_name WHERE date_of_entry < %s",
            $thirty_days_ago
        )
    );
}

<?php
// Get the lowest price in last 30 days for products which are on special
function get_30_day_price($product_id) {
	global $wpdb;

	// Step 1: Get the current sale price
	$_product = wc_get_product($product_id); // instantiate a product object
	$current_sell = $_product->get_sale_price() - 0.1; // for some reason, offset the price by 1 cent negative
	$current_oversell = $_product->get_sale_price() + 0.1; // for yet another reason, offset the price by 1 cent positive

	if (!$current_sell) { // just return a warning if there's no price
		return 'No current sale price available for this product';
	}

	// Step 2: Find the first date instance where the sale price was different from the current sale price
	$query_sale_date = $wpdb->prepare(
		"SELECT date_of_entry 
		 FROM wp_lowest_price_in_30_days
		 WHERE product_id = %d 
		 AND (sale_price < %s OR sale_price > %s)
		 ORDER BY date_of_entry DESC
		 LIMIT 1",
		$product_id,
		$current_sell,
		$current_oversell
	);

	$sale_date_result = $wpdb->get_var($query_sale_date);

	if (!$sale_date_result) { // just return a warning if there's no date
		return 'No previous different sale price found for this product';
	}

	$start_date = new DateTime($sale_date_result); // Convert the found date into a DateTime object

	// Step 3: Calculate the 30-day range before the start date
	$start_date_clone = clone $start_date; // Clone the start date to avoid modifying the original
	$start_date_clone->modify('-30 days'); // Modify it by 30 days negative
	$target_date = $start_date_clone->format('Y-m-d'); // assign it and give it the required format

	// Step 4: Query for the lowest sale price within calculated 30 day period
	$query_lowest_price = $wpdb->prepare(
		"SELECT MIN(sale_price) AS lowest_price 
		 FROM wp_lowest_price_in_30_days
		 WHERE product_id = %d 
		 AND date_of_entry BETWEEN %s AND %s
		 AND sale_price > 0",
		$product_id,
		$target_date,
		$sale_date_result // end of the range, which is the last date the product had a different sale price
	);

	$lowest_price = $wpdb->get_var($query_lowest_price);

	// Step 5: Return the lowest price
	return $lowest_price ? $lowest_price : 'No sale prices found in the 30-day period';
}
?>

<?php
echo get_30_day_price(get_the_ID()); ?>