<?php

namespace SugarCalendarBookings\Integrations\OAuthRelay;

use SugarCalendarBookings\Support\Database\AbstractTableMigration;
use WP_Error;

/**
 * OAuth Connections table migration.
 *
 * @since 1.4.0
 */
class OAuthConnectionsTableMigration extends AbstractTableMigration {

	/**
	 * Table name.
	 *
	 * @since 1.4.0
	 *
	 * @var string
	 */
	const TABLE_NAME = 'oauth_connections';

	/**
	 * Latest DB schema version.
	 *
	 * @since 1.4.0
	 * @since 1.5.0 Bumped to 2 — adds the `settings` JSON column for per-connection provider state.
	 * @since 1.5.0 Bumped to 3 — adds the indexed `subscription_id` column for webhook routing.
	 *
	 * @var int
	 */
	const LATEST_VERSION = 3;

	/**
	 * Create the table.
	 *
	 * @since 1.4.0
	 *
	 * @return WP_Error|true
	 */
	protected function migrate_to_1() {

		global $wpdb;

		$table   = $this->get_table_name();
		$collate = '';

		if ( $wpdb->has_cap( 'collation' ) ) {
			$collate = $wpdb->get_charset_collate();
		}

		$sql = "CREATE TABLE $table (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			user_id BIGINT UNSIGNED NOT NULL,
			provider VARCHAR(20) NOT NULL,
			status VARCHAR(20) NOT NULL DEFAULT 'active',
			access_token TEXT NOT NULL,
			refresh_token TEXT NOT NULL,
			expires_at DATETIME NOT NULL,
			token_type VARCHAR(20) NOT NULL DEFAULT 'bearer',
			scope TEXT NOT NULL,
			app_id INT UNSIGNED NOT NULL,
			account_id VARCHAR(100) NOT NULL,
			account_email VARCHAR(255) NOT NULL,
			account_name VARCHAR(255) NULL,
			account_avatar VARCHAR(500) NULL,
			connected_at DATETIME NOT NULL,
			refreshed_at DATETIME NULL,
			PRIMARY KEY (id),
			UNIQUE KEY user_provider_app_account (user_id, provider, app_id, account_id),
			INDEX provider (provider),
			INDEX status (status),
			INDEX idx_provider_status (provider, status)
		) {$collate};";

		dbDelta( $sql );

		return ! empty( $wpdb->last_error ) ? new WP_Error( 'migration_failed', $wpdb->last_error ) : true;
	}

	/**
	 * Add the `settings` JSON column.
	 *
	 * Stores per-connection provider state — e.g. selected calendar IDs, cached
	 * lists, webhook subscription identifiers. Shared across all OAuth providers.
	 *
	 * @since 1.5.0
	 *
	 * @return WP_Error|true
	 */
	protected function migrate_to_2() {

		global $wpdb;

		$table = $this->get_table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQLPlaceholders.UnsupportedIdentifierPlaceholder
		$column_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW COLUMNS FROM %i LIKE %s', $table, 'settings' ) );

		if ( ! empty( $column_exists ) ) {
			return true;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQLPlaceholders.UnsupportedIdentifierPlaceholder
		$wpdb->query( $wpdb->prepare( 'ALTER TABLE %i ADD COLUMN `settings` LONGTEXT NULL AFTER `refreshed_at`', $table ) );

		return ! empty( $wpdb->last_error ) ? new WP_Error( 'migration_failed', $wpdb->last_error ) : true;
	}

	/**
	 * Add the indexed `subscription_id` column.
	 *
	 * Provider webhook routing needs an equality lookup on the subscription
	 * identifier returned by `OAuthRelayClient::register_webhook()`. Promoting
	 * the value to a dedicated column with an index replaces a LIKE scan on
	 * the JSON `settings` blob and removes a JSON-shape coupling between the
	 * webhook handler and the calendar settings repository.
	 *
	 * Backfill: when the previous JSON storage carried a `subscription_id`
	 * entry, the value is copied into the new column so existing connections
	 * keep receiving webhook events without a reconnect.
	 *
	 * Self-heals a historical schema-version collision: an earlier build used
	 * schema version 2 to add a since-removed `admin_disabled` column. Installs
	 * that ran it recorded version 2, so the redefined migrate_to_2() (which
	 * adds the `settings` column) is skipped on upgrade — leaving every query
	 * below that references `settings` to fail. Running the idempotent
	 * migrate_to_2() first guarantees the column exists regardless of which
	 * version-2 history the install came from.
	 *
	 * @since 1.5.0
	 *
	 * @return WP_Error|true
	 */
	protected function migrate_to_3() {

		global $wpdb;

		$table = $this->get_table_name();

		// Ensure the `settings` column exists before anything references it.
		// migrate_to_2() is idempotent (guarded by a SHOW COLUMNS check), so
		// this is a no-op on installs that already have the column.
		$settings_result = $this->migrate_to_2();

		if ( is_wp_error( $settings_result ) ) {
			return $settings_result;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQLPlaceholders.UnsupportedIdentifierPlaceholder
		$column_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW COLUMNS FROM %i LIKE %s', $table, 'subscription_id' ) );

		if ( empty( $column_exists ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQLPlaceholders.UnsupportedIdentifierPlaceholder
			$wpdb->query( $wpdb->prepare( 'ALTER TABLE %i ADD COLUMN `subscription_id` VARCHAR(255) NULL AFTER `settings`', $table ) );

			if ( ! empty( $wpdb->last_error ) ) {
				return new WP_Error( 'migration_failed', $wpdb->last_error );
			}
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQLPlaceholders.UnsupportedIdentifierPlaceholder
		$index_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW INDEX FROM %i WHERE Key_name = %s', $table, 'idx_subscription_id' ) );

		if ( empty( $index_exists ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQLPlaceholders.UnsupportedIdentifierPlaceholder
			$wpdb->query( $wpdb->prepare( 'ALTER TABLE %i ADD INDEX `idx_subscription_id` (`subscription_id`)', $table ) );

			if ( ! empty( $wpdb->last_error ) ) {
				return new WP_Error( 'migration_failed', $wpdb->last_error );
			}
		}

		// Best-effort backfill from any settings JSON that already carried the id.
		$subscription_like = '%' . $wpdb->esc_like( '"subscription_id":' ) . '%';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery
		$wpdb->query(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnsupportedIdentifierPlaceholder
				"UPDATE %i
				SET subscription_id = TRIM(BOTH '\"' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(settings, '\"subscription_id\":', -1), ',', 1))
				WHERE subscription_id IS NULL
				  AND settings LIKE %s",
				$table,
				$subscription_like
			)
		);

		return ! empty( $wpdb->last_error ) ? new WP_Error( 'migration_failed', $wpdb->last_error ) : true;
	}
}
