<?php
namespace SabaiApps\Directories\Component\WordPressContent\SystemTool;

use SabaiApps\Directories\Component\System\Tool\AbstractTool;
use SabaiApps\Directories\Context;
use SabaiApps\Directories\Exception\RuntimeException;
use SabaiApps\Directories\Application;

class UpdateDatabaseSystemTool extends AbstractTool {

	protected function _systemToolInfo() {
		return array(
			'label'       => _x( 'Update database tables', 'tool name', 'directories-pro' ),
			'description' => __( 'This tool will update the database table schema to the latest version.', 'directories-pro' ),
			'weight'      => 200,
		);
	}

	public function systemToolSupports() {
		return $this->_application->getComponent( 'WordPressContent' )->databaseNeedsUpdate();
	}

	public function systemToolInit( array $settings, array &$storage, array &$logs, ?Context $context = null ) {
		$ret = array();
		foreach ( array(
			'field_email',
			'field_url',
			'field_phone',
			'field_icon',
			'field_claiming_status',
			'field_wp_image',
			'field_entity_term_content_count',
			'field_entity_child_count',
			'field_social_accounts',
			'field_choice',
			'field_boolean',
		) as $field_type ) {
			$ret[ $field_type ] = 1;
		}
		$ret['bundle']                            = 1;
		$ret['payment_plan_field']                = 1;
		$ret['frontendsubmit_guest_field']        = 1;
		$ret['range_field']                       = 1;
		$ret['calendar_slot_field']               = 1;
		$ret['voting_vote']                       = 1;
		$ret['paymentgateway_plan_field']         = 1;
		$ret['paymentgateway_order_field']        = 1;
		$ret['paymentgateway_subscription_field'] = 1;
		$ret['paymentgateway_discount_field']     = 1;
		$ret['social_accounts_field']             = 1;
		$ret['calendar_occurrence']               = 1;
		return $ret;
	}

	public function systemToolRunTask( $task, array $settings, $iteration, $total, array &$storage, array &$logs, ?Context $context = null ) {
		try {
			if ( strpos( $task, 'field_' ) === 0 ) {
				self::updateEntityFieldTable( $this->_application, substr( $task, strlen( 'field_' ) ), $logs );
			} else {
				switch ( $task ) {
					case 'bundle':
						self::updateEntityBundleTable( $this->_application, $logs );
						break;
					case 'payment_plan_field':
						self::updatePaymentPlanFieldTable( $this->_application, $logs );
						break;
					case 'frontendsubmit_guest_field':
						self::updateFrontendsubmitGuestFieldTable( $this->_application, $logs );
						break;
					case 'range_field':
						self::updateRangeFieldTable( $this->_application, $logs );
						break;
					case 'calendar_slot_field':
						self::updateCalendarSlotFieldTable( $this->_application, $logs );
						break;
					case 'voting_vote':
						self::updateVotingVoteTable( $this->_application, $logs );
						break;
					case 'paymentgateway_plan_field':
						self::updatePaymentGatewayPlanFieldTable( $this->_application, $logs );
						break;
					case 'paymentgateway_order_field':
						self::updatePaymentGatewayOrderFieldTable( $this->_application, $logs );
						break;
					case 'paymentgateway_subscription_field':
						self::updatePaymentGatewaySubscriptionFieldTable( $this->_application, $logs );
						break;
					case 'paymentgateway_discount_field':
						self::updatePaymentGatewayDiscountFieldTable( $this->_application, $logs );
						break;
					case 'calendar_occurrence':
						self::updateCalendarOccurrenceTable( $this->_application, $logs );
						break;
				}
			}
		} catch ( RuntimeException $e ) {
			$logs['error'][] = $e->getMessage();
		}
		return 1;
	}

	public function systemToolComplete( array &$storage, array &$logs, &$downloadFile, ?Context $context = null ) {
		$this->_application->getComponent( 'WordPressContent' )->setDatabaseVersion();
		$this->_application->Entity_Field_clearFieldSchemaCache();
	}

	public static function updateEntityBundleTable( Application $application, ?array &$logs = null ) {
		$table_name = 'drts_entity_bundle';
		if ( $table_name_full = self::_tableExists( $table_name ) ) {
			if ( self::_dropIndex( $table_name, 'bundle_component_group_type' ) ) {
				$logs['success'][] = 'Index "bundle_component_group_type" dropped from table: ' . $table_name_full;
			}
			if ( self::_addIndex( $table_name, 'bundle_type' ) ) {
				$logs['success'][] = 'Index "bundle_type" added to table: ' . $table_name_full;
			}
		}
	}

	public static function updateEntityFieldTable( Application $application, $fieldType, ?array &$logs = null ) {
		global $wpdb;
		$schema_type = 'string';
		$columns     = 'entity_type, bundle_name, entity_id, field_name, weight, value';
		switch ( $fieldType ) {
			case 'wp_image':
				$schema_type = 'wp_file';
				$columns     = 'entity_type, bundle_name, entity_id, field_name, weight, attachment_id, display_order, featured';
				break;
			case 'entity_term_content_count':
				$schema_type = 'entity_content_count';
				$columns     = 'entity_type, bundle_name, entity_id, field_name, weight, value, content_name, merged';
				$old_columns = 'entity_type, bundle_name, entity_id, field_name, weight, value, content_bundle_name, merged';
				break;
			case 'entity_child_count':
				$schema_type = 'entity_content_count';
				$columns     = 'entity_type, bundle_name, entity_id, field_name, weight, value, content_name, merged';
				$old_columns = 'entity_type, bundle_name, entity_id, field_name, weight, value, child_bundle_type, 1';
				break;
			case 'social_accounts':
				$old_columns = "entity_type, bundle_name, entity_id, field_name, weight, CONCAT(media, '|', value) AS value";
				break;
			default:
		}

		if ( $table_name = self::_tableExists( 'drts_entity_field_' . $fieldType ) ) {
			if ( ! $new_table_name = self::_tableExists( 'drts_entity_field_' . $schema_type ) ) {
				$application->Entity_Storage()->create( array( $schema_type ), array( $schema_type => array() ) ); // pass settings to force create field table
				if ( ! $new_table_name = self::_tableExists( 'drts_entity_field_' . $schema_type ) ) {
					$logs['error'][] = 'Field table for schema type ' . $schema_type . ' does not exist!';
					return;
				} else {
					$logs['success'][] = 'Field table for schema type ' . $schema_type . ' creaetd.';
				}
			}
			$sql = sprintf(
				'INSERT INTO %1$s (%2$s) SELECT %4$s FROM %3$s AS old WHERE NOT EXISTS (SELECT * from %1$s where entity_id = old.entity_id AND field_name = old.field_name)',
				$new_table_name,
				$columns,
				$table_name,
				isset( $old_columns ) ? $old_columns : $columns
			);
			if ( $num = $wpdb->query( $sql ) ) {
				$logs['success'][] = sprintf( '%d entrie(s) moved from table %s to %s.', $num, $table_name, $new_table_name );
			}
		}
		if ( false === $wpdb->update(
			$table_name = $wpdb->prefix . 'drts_entity_fieldconfig',
			array(
				'fieldconfig_schema_type' => $schema_type,
				'fieldconfig_schema'      => 'a:0:{}',
			),
			array( 'fieldconfig_type' => $fieldType )
		) ) {
			$logs['error'][] = 'Failed updating field configuration for ' . $fieldType . ' field type in table ' . $table_name . '.';
		}
	}

	public static function updateRangeFieldTable( Application $application, ?array &$logs = null ) {
		global $wpdb;
		$table_name = 'drts_entity_field_range';
		if ( $table_name_full = self::_tableExists( $table_name ) ) {
			if ( self::_addColumn( $table_name, 'average', 'DECIMAL(18,2) NOT NULL DEFAULT \'0.0\'' ) ) {
				$logs['success'][] = 'Column "average" added to table: ' . $table_name_full;
			}
			if ( self::_addIndex( $table_name, 'average' ) ) {
				$logs['success'][] = 'Index "average" added to table: ' . $table_name_full;
			}
			$sql = $wpdb->prepare(
				'UPDATE %1$s SET average = (min + max) / 2',
				$table_name_full
			);
			if ( $num = $wpdb->query( $sql ) ) {
				$logs['success'][] = sprintf( '%d entrie(s) updated in table %s.', $num, $table_name_full );
			}
		}
	}

	public static function updateCalendarSlotFieldTable( Application $application, ?array &$logs = null ) {
		global $wpdb;
		$table_name = 'drts_entity_field_calendar_slot';
		if ( $table_name_full = self::_tableExists( $table_name ) ) {
			if ( self::_changeColumnToLongText( $table_name, 'data' ) ) {
				$logs['success'][] = 'Column "data" changed to LONGTEXT in table: ' . $table_name_full;
			}
			if ( self::_addColumn( $table_name, 'all_day', 'int(1) unsigned NOT NULL DEFAULT \'0\'' ) ) {
				$logs['success'][] = 'Column "all_day" added to table: ' . $table_name_full;
			}
			if ( self::_addIndex( $table_name, 'all_day' ) ) {
				$logs['success'][] = 'Index "all_day" added to table: ' . $table_name_full;
			}
			if ( self::_dropIndex( $table_name, 'date_range' ) ) {
				$logs['success'][] = 'Index "date_range" dropped from table: ' . $table_name_full;
			}
			if ( self::_dropColumn( $table_name, 'end_date' ) ) {
				$logs['success'][] = 'Column "end_date" dropped from table: ' . $table_name_full;
			}
		}
	}

	public static function updateVotingVoteTable( Application $application, ?array &$logs = null ) {
		$table_name = 'drts_voting_vote';
		if ( $table_name_full = self::_tableExists( $table_name ) ) {
			if ( self::_addColumn( $table_name, 'vote_marked', 'int(1) unsigned NOT NULL DEFAULT \'0\'' ) ) {
				$logs['success'][] = 'Column "vote_marked" added to table: ' . $table_name_full;
			}
			if ( self::_addIndex( $table_name, 'vote_marked' ) ) {
				$logs['success'][] = 'Index "vote_marked" added to table: ' . $table_name_full;
			}
		}
	}

	public static function updatePaymentPlanFieldTable( Application $application, ?array &$logs = null ) {
		$table_name = 'drts_entity_field_payment_plan';
		if ( $table_name_full = self::_tableExists( $table_name ) ) {
			if ( self::_addColumn( $table_name, 'component', 'varchar(20) NOT NULL DEFAULT \'\'' ) ) {
				$logs['success'][] = 'Column "component" added to table: ' . $table_name_full;
			}
			if ( self::_addIndex( $table_name, 'component' ) ) {
				$logs['success'][] = 'Index "component" added to table: ' . $table_name_full;
			}
		}
	}

	public static function updateFrontendsubmitGuestFieldTable( Application $application, ?array &$logs = null ) {
		$table_name = 'drts_entity_field_frontendsubmit_guest';
		if ( $table_name_full = self::_tableExists( $table_name ) ) {
			if ( self::_addColumn( $table_name, 'guid_created', 'int(10) unsigned NOT NULL DEFAULT \'0\'' ) ) {
				$logs['success'][] = 'Column "guid_created" added to table: ' . $table_name_full;
			}
			if ( self::_addColumn( $table_name, 'ip_hash', 'varchar(64) NOT NULL DEFAULT \'\'' ) ) {
				$logs['success'][] = 'Column "ip_hash" added to table: ' . $table_name_full;
			}
		}
	}

	public static function updatePaymentGatewayPlanFieldTable( Application $application, ?array &$logs = null ) {
		global $wpdb;
		if ( ! $new_table_name = self::_tableExists( $_table_name = 'drts_entity_field_paymentgateway_plan' ) ) {
			return;
		}

		if ( self::_changeColumnToLongText( $new_table_name, 'data' ) ) {
			$logs['success'][] = 'Column "data" changed to LONGTEXT in table: ' . $new_table_name;
		}

		if ( $table_name = self::_tableExists( 'drts_paymentgateway_plan' ) ) {
			$rows = $wpdb->get_results( 'SELECT * FROM ' . $table_name );
			foreach ( array_keys( $rows ) as $i ) {
				if ( ( ! $content_bundle = $application->Entity_Bundle( $rows[ $i ]->plan_bundle_name ) )
					|| ( ! $plan_bundle = $application->Entity_Bundle( 'paymentgateway_plan', $content_bundle->group ) )
					|| ( ! $plan_data = unserialize( $rows[ $i ]->plan_data ) )
					|| empty( $rows[ $i ]->plan_plan_id )
				) {
					unset( $rows[ $i ] );
					continue;
				}
				$slug_prefix = str_replace( '_', '-', $plan_bundle->name ) . '-';
				if ( $application->Entity_Types_impl( 'post' )->entityTypeEntityBySlug( $plan_bundle->name, $slug_prefix . $rows[ $i ]->plan_name ) ) {
					continue;
				}

				try {
					$plan_entity       = $application->Entity_Save(
						$plan_bundle->name,
						array(
							'title'               => $plan_data['label'],
							'content'             => $plan_data['description'] ?? '',
							'slug'                => $slug_prefix . $rows[ $i ]->plan_name,
							'status'              => 'publish',
							'author'              => $application->getUser()->id,
							'paymentgateway_plan' => array(
								'plan_type'     => $rows[ $i ]->plan_type,
								'content_name'  => $content_bundle->type,
								'display_order' => $rows[ $i ]->plan_weight ?? 0,
								'featured'      => ! empty( $plan_data['featured'] ) ? 1 : 0,
								'active'        => ! empty( $rows[ $i ]->plan_active ) ? 1 : 0,
								'data'          => array(
									'level'        => $plan_data['level'] ?? 0,
									'price'        => $plan_data['price'] ?? 0,
									'features'     => $plan_data['features'] ?? array(),
									'feature_list' => $plan_data['feature_list'] ?? array(),
								),
							),
						),
					);
					$logs['success'][] = 'Plan ' . $rows[ $i ]->plan_name . ' moved.';
				} catch ( \Exception $e ) {
					$logs['error'][] = 'Failed to move plan ' . $rows[ $i ]->plan_name . ': ' . $e->getMessage();
					unset( $rows[ $i ] );
					continue;
				}
				if ( $result = $wpdb->query( $sql = 'UPDATE ' . $wpdb->prefix . 'drts_entity_field_payment_plan SET plan_id = ' . intval( $plan_entity->getId() ) . ' WHERE component = \'default\' AND plan_id = ' . intval( $rows[ $i ]->plan_plan_id ) ) ) {
					$logs['success'][] = 'Updated payment plan ID for plan ' . $rows[ $i ]->plan_name . '.';
				} elseif ( $result === false ) {
						$logs['error'][] = 'Failed to update payment plan ID for plan ' . $rows[ $i ]->plan_name . '. SQL: ' . $sql;
				}
			}
		}
		$plans = $application->Entity_Query( 'post' )->fieldIs( 'bundle_type', 'paymentgateway_plan' )->fetch();
		foreach ( $plans as $plan_entity ) {
			if ( ( ! $content_bundle_name = $plan_entity->getSingleFieldValue( 'paymentgateway_plan', 'content_name' ) )
				|| ( ! $content_bundle = $application->Entity_Bundle( $content_bundle_name ) ) // error or already updated using bundle type name
			) {
				continue;
			}

			try {
				$application->Entity_Save(
					$plan_entity,
					array(
						'paymentgateway_plan' => array(
							'content_name' => $content_bundle->type,
						) + $plan_entity->getSingleFieldValue( 'paymentgateway_plan' ),
					),
				);
			} catch ( \Exception $e ) {
				$logs['error'][] = 'Failed to update plan ' . $plan_entity->getTitle() . ': ' . $e->getMessage();
				continue;
			}
			$logs['success'][] = 'Plan ' . $plan_entity->getTitle() . ' updated.';
		}
	}

	public static function updatePaymentGatewayOrderFieldTable( Application $application, ?array &$logs = null ) {
		$table_name = 'drts_entity_field_paymentgateway_order';
		if ( self::_changeColumnToLongText( $table_name, 'data' ) ) {
			$logs['success'][] = 'Column "data" changed to LONGTEXT in table: ' . $table_name;
		}
		if ( self::_changeColumnToLongText( $table_name, 'notes' ) ) {
			$logs['success'][] = 'Column "notes" changed to LONGTEXT in table: ' . $table_name;
		}
	}

	public static function updatePaymentGatewaySubscriptionFieldTable( Application $application, ?array &$logs = null ) {
		$table_name = 'drts_entity_field_paymentgateway_subscription';
		if ( self::_changeColumnToLongText( $table_name, 'data' ) ) {
			$logs['success'][] = 'Column "data" changed to LONGTEXT in table: ' . $table_name;
		}
		if ( self::_changeColumnToLongText( $table_name, 'notes' ) ) {
			$logs['success'][] = 'Column "notes" changed to LONGTEXT in table: ' . $table_name;
		}
	}

	public static function updatePaymentGatewayDiscountFieldTable( Application $application, ?array &$logs = null ) {
		$table_name = 'drts_entity_field_paymentgateway_discount';
		if ( $table_name_full = self::_tableExists( $table_name ) ) {
			if ( self::_dropColumn( $table_name, 'applicable_plans' ) ) {
				$logs['success'][] = 'Column "applicable_plans" dropped from table: ' . $table_name_full;
			}

			if ( self::_changeColumnToLongText( $table_name, 'usage_history' ) ) {
				$logs['success'][] = 'Column "usage_history" changed to LONGTEXT in table: ' . $table_name_full;
			}
		}
	}

	public static function updateCalendarOccurrenceTable( Application $application, ?array &$logs = null ) {
		$table_name = 'drts_calendar_occurrence';
		if ( self::_changeColumnToLongText( $table_name, 'occurrence_slot_data_snapshot' ) ) {
			$logs['success'][] = 'Column "occurrence_slot_data_snapshot" changed to LONGTEXT in table: ' . $table_name;
		}
		if ( self::_changeColumnToLongText( $table_name, 'occurrence_data' ) ) {
			$logs['success'][] = 'Column "occurrence_data" changed to LONGTEXT in table: ' . $table_name;
		}
	}

	protected static function _addColumn( $tableName, $columnName, $columnInfo ) {
		global $wpdb;
		if ( self::_columnExists( $tableName, $columnName ) ) {
			return 0;
		}

		$tableName = $wpdb->prefix . $tableName;
		$result    = $wpdb->query( "ALTER TABLE `$tableName` ADD `$columnName` $columnInfo" );
		if ( $result === false ) {
			throw new RuntimeException( 'Failed to add index "' . $columnName . '" to ' . $tableName . ': ' . $wpdb->last_error );
		}
		return 1;
	}

	protected static function _dropColumn( $tableName, $columnName ) {
		global $wpdb;
		if ( ! self::_columnExists( $tableName, $columnName ) ) {
			return 0;
		}

		$tableName = $wpdb->prefix . $tableName;
		$result    = $wpdb->query( "ALTER TABLE `$tableName` DROP `$columnName`" );
		if ( $result === false ) {
			throw new RuntimeException( 'Failed to drop column "' . $columnName . '" from ' . $tableName . ': ' . $wpdb->last_error );
		}
		return 1;
	}

	protected static function _changeTextColumnToJson( $tableName, $columnName ) {
		global $wpdb;
		if ( ! $table_name_full = self::_tableExists( $tableName ) ) {
			return 0;
		}
		if ( self::_getColumnType( $tableName, $columnName ) === 'json' ) {
			return 0;
		}

		$new_column = $columnName . '_json';
		if ( ! self::_addColumn( $tableName, $new_column, 'JSON NOT NULL' ) ) {
			throw new RuntimeException( 'Failed to add temporary column "' . $new_column . '" to ' . $table_name_full );
		}
		$logs['success'][] = 'Column "' . $new_column . '" temporarily added to table: ' . $table_name_full;
		// Move data to the new column
		$rows = $wpdb->get_results( "SELECT number, {$columnName} FROM {$table_name_full}" );
		foreach ( $rows as $row ) {
			$original = $row->{$columnName};
			if ( empty( $original )
				|| ( ! $data = unserialize( $original ) )
			) {
				$data = array();
			}
			$json = wp_json_encode( $data );
			$wpdb->update( $table_name_full, array( $new_column => $json ), array( 'number' => $row->number ), array( '%s' ), array( '%s' ) );
		}
		if ( ! self::_dropColumn( $tableName, $columnName ) ) {
			throw new RuntimeException( 'Failed to drop column "' . $columnName . '" from ' . $table_name_full );
		}
		$result = $wpdb->query( "ALTER TABLE `$table_name_full` CHANGE `$new_column` `$columnName` JSON" );
		if ( $result === false ) {
			throw new RuntimeException( 'Failed to change column "' . $new_column . '" to "' . $columnName . '" in ' . $table_name_full . ': ' . $wpdb->last_error );
		}

		return 1;
	}

	protected static function _changeColumnToLongText( $tableName, $columnName ) {
		global $wpdb;
		if ( ! $table_name_full = self::_tableExists( $tableName ) ) {
			return 0;
		}
		$column_type = self::_getColumnType( $tableName, $columnName );
		if ( ! in_array( $column_type, array( 'text', 'json' ) ) ) {
			return 0;
		}

		if ( $column_type === 'json' ) {
			$wpdb->suppress_errors( true );
			$wpdb->query( "ALTER TABLE `$table_name_full` DROP CONSTRAINT `{$table_name_full}.{$columnName}`" );
			// do not check results as constraint may not exist
			$wpdb->suppress_errors( false );
		}
		$result = $wpdb->query( "ALTER TABLE `$table_name_full` MODIFY `$columnName` longtext NULL" );
		if ( $result === false ) {
			throw new RuntimeException( 'Failed to change column "' . $columnName . '" to LONGTEXT in ' . $table_name_full . ': ' . $wpdb->last_error );
		}

		return 1;
	}

	protected static function _addIndex( $tableName, $indexName ) {
		global $wpdb;
		if ( self::_indexExists( $tableName, $indexName ) ) {
			return 0;
		}

		$tableName = $wpdb->prefix . $tableName;
		$result    = $wpdb->query( "ALTER TABLE `$tableName` ADD INDEX `$indexName`(`$indexName`)" );
		if ( $result === false ) {
			throw new RuntimeException( 'Failed to add index "' . $indexName . '" to ' . $tableName . ': ' . $wpdb->last_error );
		}
		return 1;
	}

	protected static function _dropIndex( $tableName, $indexName ) {
		global $wpdb;
		if ( ! self::_indexExists( $tableName, $indexName ) ) {
			return 0;
		}

		$tableName = $wpdb->prefix . $tableName;
		$result    = $wpdb->query( "ALTER TABLE `$tableName` DROP INDEX `$indexName`" );
		if ( $result === false ) {
			throw new RuntimeException( 'Failed to drop index "' . $indexName . '" from ' . $tableName . ': ' . $wpdb->last_error );
		}
		return 1;
	}

	protected static function _addPrimaryKey( $tableName, array $columns ) {
		global $wpdb;
		$tableName = $wpdb->prefix . $tableName;
		$result    = $wpdb->query( "ALTER TABLE `$tableName` ADD PRIMARY KEY (`" . implode( '`, `', $columns ) . '`)' );
		if ( $result === false ) {
			throw new RuntimeException( 'Failed to add primary key to ' . $tableName . ': ' . $wpdb->last_error );
		}
		return 1;
	}

	protected static function _dropPrimaryKey( $tableName, $removeAutoIncrementKey = null ) {
		global $wpdb;
		if ( ! self::_primaryKeyExists( $tableName ) ) {
			return 0;
		}

		$tableName = $wpdb->prefix . $tableName;
		if ( ! empty( $removeAutoIncrementKey ) ) {
			$result = $wpdb->query( "ALTER TABLE `$tableName` MODIFY `$removeAutoIncrementKey` int unsigned NOT NULL" );
			if ( $result === false ) {
				throw new RuntimeException( 'Failed to remove auto_increment from ' . $tableName . ': ' . $wpdb->last_error );
			}
		}
		$result = $wpdb->query( "ALTER TABLE `$tableName` DROP PRIMARY KEY" );
		if ( $result === false ) {
			throw new RuntimeException( 'Failed to drop primary key from ' . $tableName . ': ' . $wpdb->last_error );
		}

		return 1;
	}

	protected static function _tableExists( $tableName ) {
		global $wpdb;
		$tableName = $wpdb->prefix . $tableName;
		return $wpdb->get_var( "SHOW TABLES LIKE '$tableName'" ) === $tableName ? $tableName : false;
	}

	protected static function _columnExists( $tableName, $columnName ) {
		global $wpdb;
		return (bool) $wpdb->get_var(
			"SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = '{$wpdb->dbname}' AND TABLE_NAME = '{$wpdb->prefix}$tableName' AND COLUMN_NAME = '$columnName'"
		);
	}

	protected static function _indexExists( $tableName, $indexName ) {
		global $wpdb;
		return (bool) $wpdb->get_var(
			"SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_SCHEMA = '{$wpdb->dbname}' AND TABLE_NAME = '{$wpdb->prefix}$tableName' AND INDEX_NAME = '$indexName'"
		);
	}

	protected static function _primaryKeyExists( $tableName, array $columns = array() ) {
		global $wpdb;
		if ( empty( $columns ) ) {
			return (bool) $wpdb->get_var(
				"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE TABLE_SCHEMA = '{$wpdb->dbname}' AND TABLE_NAME = '{$wpdb->prefix}$tableName' AND CONSTRAINT_TYPE = 'PRIMARY KEY'"
			);
		}
		// Check if the primary key matches the specified columns
		$sql     = "SELECT COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE
    WHERE TABLE_SCHEMA = '{$wpdb->dbname}' AND TABLE_NAME = '{$wpdb->prefix}$tableName' AND CONSTRAINT_NAME = 'PRIMARY'
    ORDER BY ORDINAL_POSITION";
		$results = $wpdb->get_col( $sql );
		return $results === $columns;
	}

	protected static function _getColumnType( $tableName, $columnName ) {
		global $wpdb;
		return $wpdb->get_var(
			"SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = '{$wpdb->dbname}' AND TABLE_NAME = '{$wpdb->prefix}$tableName' AND COLUMN_NAME = '$columnName'"
		);
	}
}
