<?php
// Exit if accessed directly.
defined( 'ABSPATH' ) || exit;

// Define database version.
define( 'WC_APPOINTMENTS_DB_VERSION', '4.6.0' );

/**
 * Installation/Migration Class.
 *
 * Handles the activation/installation of the plugin.
 *
 * @version  4.3.4
 */
class WC_Appointments_Install {
	/**
	 * Initialize hooks.
	 *
	 * @since 4.3.4
	 */
	public static function init(): void {
		self::run();
	}

	/**
	 * Run the installation.
	 *
	 * @since 4.3.4
	 */
	private static function run(): void {
		$saved_version     = get_option( 'wc_appointments_version' );
		$installed_version = $saved_version ?: WC_APPOINTMENTS_VERSION;

		// Check the version before running.
		if ( ! defined( 'IFRAME_REQUEST' ) && WC_APPOINTMENTS_VERSION !== $saved_version ) {
			// Flag legacy installs so pricing defaults can preserve prior behavior.
			// We only set this once, on upgrade from a version older than 5.0.0.
			$legacy_flag = get_option( 'wc_appointments_legacy_pricing_defaults', '' );
			if ( '' === $legacy_flag ) {
				if ( $saved_version && version_compare( $saved_version, '5.0.0', '<' ) ) {
					add_option( 'wc_appointments_legacy_pricing_defaults', 'yes' );
				} else {
					add_option( 'wc_appointments_legacy_pricing_defaults', 'no' );
				}
			}

			// Store when plugin was updated to 5.0.0 (for per-product pricing logic)
			// Products modified after this time use interval-based, before use duration-based
			if ( version_compare( WC_APPOINTMENTS_VERSION, '5.0.0', '>=' ) && ( ! $saved_version || version_compare( $saved_version, '5.0.0', '<' ) ) ) {
				$update_time = get_option( 'wc_appointments_version_5_0_0_update_time', '' );
				if ( '' === $update_time ) {
					add_option( 'wc_appointments_version_5_0_0_update_time', current_time( 'mysql', true ) );
				}
			}

			// Removed "What's New" landing page trigger for upgrades
			if ( ! defined( 'WC_APPOINTMENTS_INSTALLING' ) ) {
				define( 'WC_APPOINTMENTS_INSTALLING', true );
			}

			self::update_plugin_version();
			self::update_db_version();

			global $wpdb, $wp_roles;

			$wpdb->hide_errors();

			$collate = '';

			if ( $wpdb->has_cap( 'collation' ) ) {
				if ( ! empty( $wpdb->charset ) ) {
					$collate .= "DEFAULT CHARACTER SET $wpdb->charset";
				}
				if ( ! empty( $wpdb->collate ) ) {
					$collate .= " COLLATE $wpdb->collate";
				}
			}

			require_once ABSPATH . 'wp-admin/includes/upgrade.php';

			dbDelta(
			    "CREATE TABLE {$wpdb->prefix}wc_appointment_relationships (
					ID bigint(20) unsigned NOT NULL auto_increment,
					product_id bigint(20) unsigned NOT NULL,
					staff_id bigint(20) unsigned NOT NULL,
					sort_order bigint(20) unsigned NOT NULL default 0,
					PRIMARY KEY  (ID),
					KEY product_id (product_id),
					KEY staff_id (staff_id)
				) $collate;
				CREATE TABLE {$wpdb->prefix}wc_appointments_availability (
					ID bigint(20) unsigned NOT NULL auto_increment,
					kind varchar(100) NOT NULL,
					kind_id varchar(100) NOT NULL,
					event_id varchar(255) NOT NULL,
					parent_event_id varchar(255) NULL default NULL,
					title varchar(255) NULL,
					range_type varchar(60) NOT NULL,
					from_date varchar(60) NOT NULL,
					to_date varchar(60) NOT NULL,
					from_range varchar(60) NULL,
					to_range varchar(60) NULL,
					appointable varchar(5) NOT NULL default 'yes',
					priority int(2) NOT NULL default 10,
					qty bigint(20) NOT NULL,
					ordering int(2) NOT NULL default 0,
					date_created datetime NULL default NULL,
					date_modified datetime NULL default NULL,
				    rrule text NULL default NULL,
					PRIMARY KEY  (ID),
					KEY kind_id (kind_id)
				) $collate;
				CREATE TABLE {$wpdb->prefix}wc_appointments_availability_cache (
				id bigint(20) unsigned NOT NULL auto_increment,
				source enum('availability','appointment') NOT NULL,
				source_id bigint(20) unsigned NOT NULL,
				product_id bigint(20) unsigned NOT NULL default 0,
				staff_id bigint(20) unsigned NOT NULL default 0,
				scope enum('global','product','staff') NOT NULL default 'global',
				appointable varchar(5) NOT NULL default 'yes',
				priority int(11) NOT NULL default 10,
				ordering int(2) NOT NULL default 0,
				qty bigint(20) NOT NULL default 0,
				start_ts bigint(20) NOT NULL,
				end_ts bigint(20) NOT NULL,
				range_type varchar(60) NULL,
				rule_kind varchar(100) NULL,
				status varchar(100) NULL,
				date_created datetime NULL default NULL,
				date_modified datetime NULL default NULL,
					PRIMARY KEY (id),
					KEY src (source, source_id),
					KEY time_idx (start_ts, end_ts),
					KEY product_staff (product_id, staff_id),
					KEY avail_global_lookup (source, scope, start_ts, end_ts, staff_id, priority),
					KEY avail_product_lookup (source, scope, product_id, start_ts, end_ts, staff_id, priority),
					KEY avail_staff_lookup (source, scope, staff_id, start_ts, end_ts, priority),
					KEY priority_sort (priority DESC),
					UNIQUE KEY uniq_occurrence (source, source_id, product_id, staff_id, start_ts, end_ts)
				) $collate;",
			);

			// Product type.
			if ( ! get_term_by( 'slug', sanitize_title( 'appointment' ), 'product_type' ) ) {
				wp_insert_term( 'appointment', 'product_type' );
			}

			// Capabilities.
			if ( class_exists('WP_Roles') && ! isset( $wp_roles ) ) {
				$wp_roles = new WP_Roles();
			}

			// Shop staff role.
			add_role(
			    'shop_staff',
			    __( 'Shop Staff', 'woocommerce-appointments' ),
			    [
				'level_8'                   => true,
				'level_7'                   => true,
				'level_6'                   => true,
				'level_5'                   => true,
				'level_4'                   => true,
				'level_3'                   => true,
				'level_2'                   => true,
					'level_1'                   => true,
					'level_0'                   => true,

					'read'                      => true,

					'read_private_posts'        => true,
					'edit_posts'                => true,
					'edit_published_posts'      => true,
					'edit_private_posts'        => true,
					'edit_others_posts'         => false,
					'publish_posts'             => true,
					'delete_private_posts'      => true,
					'delete_posts'              => true,
					'delete_published_posts'    => true,
					'delete_others_posts'       => false,

					'read_private_pages'        => true,
					'edit_pages'                => true,
					'edit_published_pages'      => true,
					'edit_private_pages'        => true,
					'edit_others_pages'         => false,
					'publish_pages'             => true,
					'delete_pages'              => true,
					'delete_private_pages'      => true,
					'delete_published_pages'    => true,
					'delete_others_pages'       => false,

					'read_private_products'     => true,
					'edit_products'             => true,
					'edit_published_products'   => true,
					'edit_private_products'     => true,
					'edit_others_products'      => false,
					'publish_products'          => true,
					'delete_products'           => true,
					'delete_private_products'   => true,
					'delete_published_products' => true,
					'delete_others_products'    => false,
					'edit_shop_orders'          => true,
					'edit_others_shop_orders'   => true,

					'manage_categories'         => false,
					'manage_links'              => false,
					'moderate_comments'         => true,
					'unfiltered_html'           => true,
					'upload_files'              => true,
					'export'                    => false,
					'import'                    => false,

					'edit_users'                => true,
					'list_users'                => true,
				],
			);

			if ( is_object( $wp_roles ) ) {
				// Ability to manage appointments.
				$wp_roles->add_cap( 'shop_manager', 'manage_appointments' );
				$wp_roles->add_cap( 'administrator', 'manage_appointments' );
				$wp_roles->add_cap( 'shop_staff', 'manage_appointments' );

				// Ability to edit their shop orders.
				$wp_roles->add_cap( 'shop_staff', 'edit_shop_orders' );
				$wp_roles->add_cap( 'shop_manager', 'edit_shop_orders' );

				// Ability to edit others shop orders.
				$wp_roles->add_cap( 'shop_staff', 'edit_others_shop_orders' );
				$wp_roles->add_cap( 'shop_manager', 'edit_others_shop_orders' );

				// Ability to view others appointments.
				$wp_roles->add_cap( 'shop_manager', 'manage_others_appointments' );
				$wp_roles->add_cap( 'administrator', 'manage_others_appointments' );
				$wp_roles->remove_cap( 'shop_staff', 'manage_others_appointments' );
			}

			// Shop staff expand capabilities.
			$capabilities         = [];
			$capabilities['core'] = [
				'view_woocommerce_reports',
			];

			$capability_types = [
				'appointment',
			];
			foreach ( $capability_types as $capability_type ) {
				$capabilities[ $capability_type ] = [
					// Post type
					"edit_{$capability_type}",
					"read_{$capability_type}",
					"delete_{$capability_type}",
					"edit_{$capability_type}s",
					"edit_others_{$capability_type}s",
					"publish_{$capability_type}s",
					"read_private_{$capability_type}s",
					"delete_{$capability_type}s",
					"delete_private_{$capability_type}s",
					"delete_published_{$capability_type}s",
					"delete_others_{$capability_type}s",
					"edit_private_{$capability_type}s",
					"edit_published_{$capability_type}s",

					// Terms
					"manage_{$capability_type}_terms",
					"edit_{$capability_type}_terms",
					"delete_{$capability_type}_terms",
					"assign_{$capability_type}_terms",
				];
			}

			foreach ( $capabilities as $cap_group ) {
				foreach ( $cap_group as $cap ) {
					$wp_roles->add_cap( 'shop_staff', $cap );
					$wp_roles->add_cap( 'shop_manager', $cap );
					$wp_roles->add_cap( 'administrator', $cap );
				}
			}

			// Update 5.1.2
			if ( version_compare( $installed_version, '5.1.2', '<' ) ) {
				self::migration_5_1_2();
			}

			// Update 4.21.3
			if ( version_compare( $installed_version, '4.22.0', '<' ) ) {
				// Flush Permalinks.
				flush_rewrite_rules();
			}

			// Update 4.21.3
			if ( version_compare( $installed_version, '4.21.3', '<' ) ) {
				// Flush Permalinks.
				flush_rewrite_rules();
			}

			// Update 4.5.1
			if ( version_compare( $installed_version, '4.5.1', '<' ) ) {
				self::migration_4_5_1();
			}

			// Update 4.4.0
			if ( version_compare( $installed_version, '4.4.0', '<' ) ) {
				self::migration_4_4_0();
			}

			// Update 4.1.5
			if ( version_compare( get_option( 'wc_appointments_version', WC_APPOINTMENTS_VERSION ), '4.1.5', '<' ) ) {
				self::migration_4_1_5();
			}

			// Update 3.4.0
			if ( version_compare( get_option( 'wc_appointments_version', WC_APPOINTMENTS_VERSION ), '3.4', '<' ) ) {
				self::migration_3_4_0();
			}

			// Update 3.7.0
			if ( version_compare( get_option( 'wc_appointments_version', WC_APPOINTMENTS_VERSION ), '3.7', '<' ) ) {
				self::migration_3_7_0();
			}

			// Check template versions.
			if ( class_exists( 'WC_Appointments_Admin' ) ) {
				WC_Appointments_Admin::template_file_check_notice();
			}

			self::cleanup_empty_availability_rules();

			// Ensure postmeta indexes exist for appointment date queries
			self::ensure_postmeta_indexes();

			do_action( 'wc_appointments_updated' );
		}
	}

	/**
	 * Updates the plugin version in db.
	 *
	 * @since 4.3.4
	 */
	private static function update_plugin_version(): void {
		delete_option( 'wc_appointments_version' );
		add_option( 'wc_appointments_version', WC_APPOINTMENTS_VERSION );
	}

	/**
	 * Updates the plugin db version in db.
	 *
	 * @since 4.3.4
	 */
	private static function update_db_version(): void {
		delete_option( 'wc_appointments_db_version' );
		add_option( 'wc_appointments_db_version', WC_APPOINTMENTS_DB_VERSION );
	}

	/**
	 * Trash all gcal "fake" appointments and move gcal settings.
	 *
	 * @since 3.7.0
	 */
	private static function migration_3_7_0(): void {
		global $wpdb;

		// Trash all GCal appointments.
		$gcal_id = apply_filters( 'woocommerce_appointments_gcal_synced_product_id', 2147483647 );

		$wpdb->query(
		    "UPDATE {$wpdb->posts} as posts
			LEFT JOIN {$wpdb->postmeta} AS meta ON posts.ID = meta.post_id
			SET posts.post_status = 'trash'
			WHERE posts.post_type = 'wc_appointment'
				AND meta.meta_key = '_appointment_product_id'
				AND meta.meta_value = {$gcal_id}",
		);

		// Delete full sync checking.
		wp_clear_scheduled_hook( 'wc-appointment-sync-full-from-gcal' );

		// Move Google Calendar Integration settings.
		$old_gcal_settings = get_option( 'wc_appointments_gcal_settings' );
		if ( $old_gcal_settings ) {
			foreach ( $old_gcal_settings as $old_gcal_setting_key => $old_gcal_setting_value ) {
				switch ( $old_gcal_setting_key ) {
					case 'client_id':
						add_option( 'wc_appointments_gcal_client_id', $old_gcal_setting_value );
						break;
					case 'client_secret':
						add_option( 'wc_appointments_gcal_client_secret', $old_gcal_setting_value );
						break;
					case 'calendar_id':
						add_option( 'wc_appointments_gcal_calendar_id', $old_gcal_setting_value );
						break;
					case 'authorization':
						add_option( 'wc_appointments_gcal_authorization', $old_gcal_setting_value );
						break;
					case 'twoway':
						add_option( 'wc_appointments_gcal_twoway', $old_gcal_setting_value );
						break;
					case 'debug':
						add_option( 'wc_appointments_gcal_debug', $old_gcal_setting_value );
						break;
				}
			}
			#delete_option( 'wc_appointments_gcal_settings' );
		}
	}

	/**
	 * Change appointment status from "pending" to "pending-confirmation".
	 *
	 * @since 3.4.0
	 */
	private static function migration_3_4_0(): void {
		global $wpdb;

		$wpdb->query(
		    "UPDATE {$wpdb->posts} as posts
			SET posts.post_status = 'pending-confirmation'
			WHERE posts.post_type = 'wc_appointment'
			AND posts.post_status = 'pending';",
		);
	}

	/**
	 * Trash duplicated appointments for WPML.
	 *
	 * @since 4.1.5
	 */
	private static function migration_4_1_5(): void {
		global $wpdb;

		if ( class_exists( 'SitePress' ) && class_exists( 'woocommerce_wpml' ) && class_exists( 'WPML_Element_Translation_Package' ) ) {
			$wpdb->query(
			    "UPDATE {$wpdb->posts} as posts
				LEFT JOIN {$wpdb->prefix}icl_translations AS translations ON translations.element_id = posts.id
				SET posts.post_status = 'trash'
				WHERE posts.post_type = 'wc_appointment'
					AND translations.element_type = 'post_wc_appointment'
					AND translations.source_language_code != ''
				",
			);
		}
	}

	/**
	 * Migrate global availabiltity from options table
	 * to custom availability table.
	 *
	 * @since 4.3.4
	 */
	private static function migration_4_4_0(): void {
		global $wpdb;

		// Get 'wc_appointments_gcal_twoway' option.
		$two_way_option = get_option( 'wc_appointments_gcal_twoway' );

		// Migrate global availabilities.
		$global_availability = get_option( 'wc_global_appointment_availability', [] );

		if ( ! empty( $global_availability ) ) {
			$index = 0;

			foreach ( $global_availability as $rule ) {
				$type        = empty( $rule['type'] ) ? '' : $rule['type'];
				$from_range  = empty( $rule['from'] ) ? '' : $rule['from'];
				$to_range    = empty( $rule['to'] ) ? '' : $rule['to'];
				$from_date   = empty( $rule['from_date'] ) ? '' : $rule['from_date'];
				$to_date     = empty( $rule['to_date'] ) ? '' : $rule['to_date'];
				$appointable = empty( $rule['appointable'] ) ? '' : $rule['appointable'];
				$priority    = empty( $rule['priority'] ) ? 10 : $rule['priority'];

				$wpdb->insert(
				    $wpdb->prefix . 'wc_appointments_availability',
				    [
						'kind'          => 'availability#global',
						'kind_id'       => '',
						'title'         => '',
						'range_type'    => $type,
						'from_range'    => $from_range,
						'to_range'      => $to_range,
						'from_date'     => $from_date,
						'to_date'       => $to_date,
						'appointable'   => $appointable,
						'priority'      => $priority,
						'ordering'      => $index,
						'rrule'         => '',
						'date_created'  => current_time( 'mysql' ),
						'date_modified' => current_time( 'mysql' ),
					],
				);

				$index++;
			}

			// When migrated, delete old availability rules.
			delete_option( 'wc_global_appointment_availability' );
		}

		// Migrate product availabilities.
		$all_product_args = [
			'post_status'      => 'publish',
			'post_type'        => 'product',
			'posts_per_page'   => -1,
			'suppress_filters' => true,
			'fields'           => 'ids',
			'orderby'          => 'title',
			'order'            => 'ASC',
		];
		$posts_query      = new WP_Query();
	    $all_product_ids  = $posts_query->query( $all_product_args );

		if ( $all_product_ids ) {
			foreach ( $all_product_ids as $all_product_id ) {
				$product_availabilities = get_post_meta( $all_product_id, '_wc_appointment_availability', true );

				if ( $product_availabilities && is_array( $product_availabilities ) && [] !== $product_availabilities ) {
					$index_p = 0;

					foreach ( $product_availabilities as $rule ) {
						$type        = empty( $rule['type'] ) ? '' : $rule['type'];
						$from_range  = empty( $rule['from'] ) ? '' : $rule['from'];
						$to_range    = empty( $rule['to'] ) ? '' : $rule['to'];
						$from_date   = empty( $rule['from_date'] ) ? '' : $rule['from_date'];
						$to_date     = empty( $rule['to_date'] ) ? '' : $rule['to_date'];
						$appointable = empty( $rule['appointable'] ) ? '' : $rule['appointable'];
						$priority    = empty( $rule['priority'] ) ? 10 : $rule['priority'];

						$wpdb->insert(
						    $wpdb->prefix . 'wc_appointments_availability',
						    [
								'kind'          => 'availability#product',
								'kind_id'       => $all_product_id,
								'title'         => '',
								'range_type'    => $type,
								'from_range'    => $from_range,
								'to_range'      => $to_range,
								'from_date'     => $from_date,
								'to_date'       => $to_date,
								'appointable'   => $appointable,
								'priority'      => $priority,
								'ordering'      => $index_p,
								'rrule'         => '',
								'date_created'  => current_time( 'mysql' ),
								'date_modified' => current_time( 'mysql' ),
							],
						);

						$index_p++;
					}

					// When migrated, delete old availability rules.
					delete_post_meta( $all_product_id, '_wc_appointment_availability', $rule );
				} else {
					continue;
				}
			}
		}

		// Migrate staff availabilities and settings.
		$all_staff = get_users(
		    [
				'role'    => 'shop_staff',
				'orderby' => 'nicename',
				'order'   => 'asc',
			],
		);

		if ( $all_staff ) {
			foreach ( $all_staff as $single_staff ) {
				// Get single staff availability rules.
				$single_staff_availabilities = get_user_meta( $single_staff->ID, '_wc_appointment_availability', true );

				// Get single staff availability rules.
				$single_staff_gcal_calendar_id = get_user_meta( $single_staff->ID, 'wc_appointments_gcal_calendar_id', true );

				// Update single staff two_way option.
				if ( 'yes' === $two_way_option ) {
					update_user_meta( $single_staff->ID, 'wc_appointments_gcal_twoway', 'two_way' );
				} elseif ( 'no' === $two_way_option ) {
					update_user_meta( $single_staff->ID, 'wc_appointments_gcal_twoway', 'one_way' );
				}

				if ( $single_staff_availabilities && is_array( $single_staff_availabilities ) && [] !== $single_staff_availabilities ) {
					$index_s = 0;

					foreach ( $single_staff_availabilities as $rule ) {
						$type        = empty( $rule['type'] ) ? '' : $rule['type'];
						$from_range  = empty( $rule['from'] ) ? '' : $rule['from'];
						$to_range    = empty( $rule['to'] ) ? '' : $rule['to'];
						$from_date   = empty( $rule['from_date'] ) ? '' : $rule['from_date'];
						$to_date     = empty( $rule['to_date'] ) ? '' : $rule['to_date'];
						$appointable = empty( $rule['appointable'] ) ? '' : $rule['appointable'];
						$priority    = empty( $rule['priority'] ) ? 10 : $rule['priority'];

						$wpdb->insert(
						    $wpdb->prefix . 'wc_appointments_availability',
						    [
								'kind'          => 'availability#staff',
								'kind_id'       => $single_staff->ID,
								'title'         => '',
								'range_type'    => $type,
								'from_range'    => $from_range,
								'to_range'      => $to_range,
								'from_date'     => $from_date,
								'to_date'       => $to_date,
								'appointable'   => $appointable,
								'priority'      => $priority,
								'ordering'      => $index_s,
								'rrule'         => '',
								'date_created'  => current_time( 'mysql' ),
								'date_modified' => current_time( 'mysql' ),
							],
						);

						$index_s++;
					}

					// When migrated, delete old availability rules.
					delete_user_meta( $single_staff->ID, '_wc_appointment_availability' );
				} else {
					continue;
				}
			}
		}

		// Update 'wc_appointments_gcal_twoway' option.
		if ( 'yes' === $two_way_option ) {
			update_option( 'wc_appointments_gcal_twoway', 'two_way' );
		} elseif ( 'no' === $two_way_option ) {
			update_option( 'wc_appointments_gcal_twoway', 'one_way' );
		}

		// Stop syncing via WP Cron.
		wp_clear_scheduled_hook( 'wc-appointment-sync-from-gcal' );
		wp_clear_scheduled_hook( 'wc-appointment-complete' );
		wp_clear_scheduled_hook( 'wc-appointment-reminder' );
		wp_clear_scheduled_hook( 'wc-appointment-remove-inactive-cart' );
	}

	/**
	 * Remove as scheduled action.
	 *
	 * @since 4.5.1
	 */
	private static function migration_4_5_1(): void {
		if ( ! defined( 'EMPTY_TRASH_DAYS' ) ) {
			define( 'EMPTY_TRASH_DAYS', 30 );
		}

		if ( function_exists( 'as_unschedule_all_actions' ) ) {
			as_unschedule_all_actions( 'wc-appointment-sync-from-gcal' );
		}
	}

	/**
	 * Add parent_event_id column to availability table.
	 *
	 * @since 5.1.2
	 */
	private static function migration_5_1_2(): void {
		global $wpdb;
		$table = $wpdb->prefix . 'wc_appointments_availability';
		
		// Check if parent_event_id column exists
		$column_exists = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
				 WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = %s",
				DB_NAME,
				$table,
				'parent_event_id'
			)
		);

		// Add column if it doesn't exist
		if ( empty( $column_exists ) ) {
			$wpdb->query(
				"ALTER TABLE {$table} 
				 ADD COLUMN parent_event_id VARCHAR(255) NULL DEFAULT NULL AFTER event_id"
			);
		}
	}

	/**
	 * Ensure required indexes and unique keys exist on the availability cache table.
	 * Safe to call multiple times.
	 */
	public static function ensure_availability_cache_indexes(): void {
		global $wpdb;
		$table = $wpdb->prefix . 'wc_appointments_availability_cache';

		// Helper to detect existing index by name.
		$get_indexes = $wpdb->get_results( $wpdb->prepare( "SHOW INDEX FROM {$table} WHERE Key_name = %s", 'uniq_src_srcid_staff_prod_time_status' ) );
		if ( empty( $get_indexes ) ) {
			$wpdb->query(
			    "ALTER TABLE {$table}
				 ADD UNIQUE KEY uniq_src_srcid_staff_prod_time_status
				 (source, source_id, staff_id, product_id, start_ts, end_ts, status)",
			);
		}

		// Support queries by staff/time.
		if ( empty( $wpdb->get_results( $wpdb->prepare( "SHOW INDEX FROM {$table} WHERE Key_name = %s", 'idx_src_staff_time' ) ) ) ) {
			$wpdb->query(
			    "ALTER TABLE {$table}
				 ADD INDEX idx_src_staff_time (source, staff_id, start_ts, end_ts)",
			);
		}

		// Support queries by product/time.
		if ( empty( $wpdb->get_results( $wpdb->prepare( "SHOW INDEX FROM {$table} WHERE Key_name = %s", 'idx_src_product_time' ) ) ) ) {
			$wpdb->query(
			    "ALTER TABLE {$table}
				 ADD INDEX idx_src_product_time (source, product_id, start_ts, end_ts)",
			);
		}

		// Support rules/status filtering.
		if ( empty( $wpdb->get_results( $wpdb->prepare( "SHOW INDEX FROM {$table} WHERE Key_name = %s", 'idx_status' ) ) ) ) {
			$wpdb->query(
			    "ALTER TABLE {$table}
				 ADD INDEX idx_status (status)",
			);
		}

		// Optimize get_qty_input_max() query: covers source, qty, appointable, scope filters
		// This index is specifically optimized for the MAX(qty) query with scope OR conditions
		$qty_max_index = 'idx_qty_max_lookup';
		if ( empty( $wpdb->get_results( $wpdb->prepare( "SHOW INDEX FROM {$table} WHERE Key_name = %s", $qty_max_index ) ) ) ) {
			$wpdb->query(
			    "ALTER TABLE {$table}
				 ADD INDEX {$qty_max_index} (source, qty, appointable, scope, product_id, staff_id, source_id)",
			);
		}
	}

	/**
	 * Ensure required indexes exist on postmeta table for appointment queries.
	 * Safe to call multiple times.
	 *
	 * This optimizes all appointment-related postmeta queries by creating composite
	 * indexes:
	 *
	 * 1. (meta_key, meta_value) - Helps with:
	 *    - Date range queries: _appointment_start, _appointment_end (comparisons: <, >, <=, >=)
	 *    - ID lookups: _appointment_product_id, _appointment_staff_id, _appointment_customer_id,
	 *      _appointment_order_item_id (equality and IN clauses)
	 *    - Google Calendar: _wc_appointments_gcal_event_id (IN clauses)
	 *    - Other meta: _appointment_all_day (equality checks)
	 *
	 * 2. (post_id, meta_key) - Helps with:
	 *    - JOIN operations: When joining postmeta on post_id and filtering by meta_key
	 *    - Significantly speeds up queries with multiple LEFT JOINs on postmeta
	 *    - Used in get_appointment_ids_by() when querying by customer, product, staff, dates
	 *
	 * These indexes allow MySQL to efficiently:
	 * - Filter by meta_key first, then perform comparisons/lookups on meta_value
	 * - Join on post_id first, then filter by meta_key (much faster than scanning all postmeta)
	 */
		public static function ensure_postmeta_indexes(): void {
			global $wpdb;
			$table = $wpdb->postmeta;

			// Check if composite index on (meta_key, meta_value) exists
			// This index helps with all queries that filter by meta_key and compare/lookup meta_value
			$index_name = 'wc_appointments_meta_key_value';
			$existing_index = $wpdb->get_results(
			    $wpdb->prepare(
			        "SHOW INDEX FROM {$table} WHERE Key_name = %s",
			        $index_name,
			    ),
			);

			$created_key = false;
			if ( empty( $existing_index ) ) {
				// Create composite index on (meta_key, meta_value)
				// This will speed up queries filtering by meta_key and comparing/looking up meta_value
				// Using meta_value(20) prefix which is sufficient for:
				// - Date strings: YmdHis format (14 chars)
				// - Integer IDs: typically 1-10 digits
				// - Boolean values: 0/1 (1 char)
				$wpdb->query(
				    "ALTER TABLE {$table}
					 ADD INDEX {$index_name} (meta_key(191), meta_value(20))",
				);
				$created_key = true;
			}

			// Check if composite index on (post_id, meta_key) exists
			// This index is critical for JOIN performance when querying appointments
			$index_name_join = 'wc_appointments_post_id_meta_key';
			$existing_index_join = $wpdb->get_results(
			    $wpdb->prepare(
			        "SHOW INDEX FROM {$table} WHERE Key_name = %s",
			        $index_name_join,
			    ),
			);

			$created_join = false;
			if ( empty( $existing_index_join ) ) {
				// Create composite index on (post_id, meta_key)
				// This dramatically speeds up LEFT JOIN operations in get_appointment_ids_by()
				// When joining multiple postmeta tables (customer_id, start, end, etc.)
				// MySQL can use this index to quickly find matching rows by post_id, then filter by meta_key
				$wpdb->query(
				    "ALTER TABLE {$table}
					 ADD INDEX {$index_name_join} (post_id, meta_key(191))",
				);
				$created_join = true;
			}

			// Verify only when creation was attempted
			if ( $created_key ) {
				$check_key = $wpdb->get_results(
				    $wpdb->prepare(
				        "SHOW INDEX FROM {$table} WHERE Key_name = %s",
				        $index_name,
				    ),
				);
				if ( empty( $check_key ) ) {
					error_log( 'WC Appointments: Warning - postmeta meta_key/meta_value index creation may have failed.' );
				}
			}
			if ( $created_join ) {
				$check_join = $wpdb->get_results(
				    $wpdb->prepare(
				        "SHOW INDEX FROM {$table} WHERE Key_name = %s",
				        $index_name_join,
				    ),
				);
				if ( empty( $check_join ) ) {
					error_log( 'WC Appointments: Warning - post_id/meta_key index creation may have failed.' );
				}
			}
		}

	/**
	 * Ensure indexes.
	 *
	 * Checks and ensures availability cache indexes exist on plugin load.
	 */
	public static function maybe_ensure_indexes(): void {
		$flag = 'wc_appointments_availability_cache_indexes_done';
		if ( get_option( $flag ) ) {
			return;
		}
		self::ensure_availability_cache_indexes();
		update_option( $flag, 1, false );
	}

	/**
	 * Ensure postmeta indexes.
	 *
	 * Checks and ensures postmeta indexes exist on plugin load.
	 */
	public static function maybe_ensure_postmeta_indexes(): void {
		$flag = 'wc_appointments_postmeta_indexes_done';
		// Always check and create indexes if missing (in case they were dropped)
		self::ensure_postmeta_indexes();
		// Only set flag after successful creation to allow re-checking
		if ( ! get_option( $flag ) ) {
			update_option( $flag, 1, false );
		}
	}

	/**
	 * Cleanup empty availability rules.
	 *
	 * Removes empty or invalid availability rules from the database.
	 */
	private static function cleanup_empty_availability_rules(): void {
		global $wpdb;
		$table = $wpdb->prefix . 'wc_appointments_availability';
		$wpdb->query( "DELETE FROM {$table} WHERE (range_type = '' OR (IFNULL(from_date,'') = '' AND IFNULL(to_date,'') = '' AND IFNULL(from_range,'') = '' AND IFNULL(to_range,'') = '')) AND event_id = ''" );
	}
}

add_action( 'plugins_loaded', [ 'WC_Appointments_Install', 'maybe_ensure_indexes' ], 20 );
add_action( 'plugins_loaded', [ 'WC_Appointments_Install', 'maybe_ensure_postmeta_indexes' ], 20 );
