<?php
/**
 * Change the time zone of all events and adjust the UTC times accordingly.
 *
 * Usage: Add the snippet with Code Snippets.
 *        Set your target time zone on line 20.
 *        Optional: Set your target time zone abbreviation on line 33.
 *        Save it as a 'Run once' snippet.
 *        Go to the Snippet list and click on 'Run' next to the snippet.
 *
 * @author: Andras Guseo
 *
 * Plugins required: The Events Calendar
 *
 * @since January, 2025 Initial version.
 */

add_action( 'admin_init', function () {
	global $wpdb;

	// Set a valid time zone.
	$target_tz = 'Europe/Zurich';

	// Bail if not a valid time zone
	if ( ! in_array( $target_tz, timezone_identifiers_list() ) ) {
		return;
	}

	$date = new DateTime( 'now', new DateTimeZone( $target_tz ) );

	// Note, not all time zones have a CET format abbreviation in PHP.
	// You can also set this manually.
	// $target_tz_abbr = 'CET';
	$target_tz_abbr = $date->format( 'T' );

	// Update all meta values to the target time zone where the meta_key is '_EventTimeZone'
	$wpdb->query(
		$wpdb->prepare(
			"UPDATE {$wpdb->postmeta} SET meta_value = %s WHERE meta_key = %s",
			$target_tz,
			'_EventTimeZone'
		)
	);

	// Update all meta values to the new abbreviation where the meta_key is '_EventTimeZoneAbbr'
	$wpdb->query(
		$wpdb->prepare(
			"UPDATE {$wpdb->postmeta} SET meta_value = %s WHERE meta_key = %s",
			$target_tz_abbr,
			'_EventTimeZoneAbbr'
		)
	);

	// Get all '_EventStartDate' and '_EventEndDate' meta values and their post IDs
	$start_dates = $wpdb->get_results( "
        SELECT post_id, meta_value
        FROM {$wpdb->postmeta}
        WHERE meta_key = '_EventStartDate'
    " );

	$end_dates = $wpdb->get_results( "
        SELECT post_id, meta_value
        FROM {$wpdb->postmeta}
        WHERE meta_key = '_EventEndDate'
    " );

	// Timezone conversion function
	function convert_to_utc( $datetime, $timezone = 'UTC' ) {
		try {
			$date = new DateTime( $datetime, new DateTimeZone( $timezone ) );
			$date->setTimezone( new DateTimeZone( 'UTC' ) );

			return $date->format( 'Y-m-d H:i:s' );
		} catch ( Exception $e ) {
			return false; // Return false on failure
		}
	}

	// Update '_EventStartDateUTC' based on '_EventStartDate'
	foreach ( $start_dates as $start_date ) {
		$utc_time = convert_to_utc( $start_date->meta_value, $target_tz );
		if ( $utc_time ) {
			$wpdb->update(
				$wpdb->postmeta,
				[ 'meta_value' => $utc_time ],
				[ 'post_id' => $start_date->post_id, 'meta_key' => '_EventStartDateUTC' ],
				[ '%s' ],
				[ '%d', '%s' ]
			);
		}
	}

	// Update '_EventEndDateUTC' based on '_EventEndDate'
	foreach ( $end_dates as $end_date ) {
		$utc_time = convert_to_utc( $end_date->meta_value, $target_tz );
		if ( $utc_time ) {
			$wpdb->update(
				$wpdb->postmeta,
				[ 'meta_value' => $utc_time ],
				[ 'post_id' => $end_date->post_id, 'meta_key' => '_EventEndDateUTC' ],
				[ '%s' ],
				[ '%d', '%s' ]
			);
		}
	}
} );