<?php

namespace SugarCalendarBookings\Integrations\Webhooks;

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

/**
 * Webhook subscriptions table migration.
 *
 * Stores one provider-issued push subscription per (connection, watched
 * resource), mapping the subscription id back to the resource it watches. The
 * `resource_id` column is generic (calendar id for Outlook/Google, empty for
 * connection-wide subscriptions) so multiple calendar providers can share the
 * table; `provider` is denormalized so a renewal scanner can group and
 * dispatch without joining `oauth_connections`.
 *
 * @since 1.5.0
 */
class WebhookSubscriptionsTableMigration extends AbstractTableMigration {

	/**
	 * Table name.
	 *
	 * @since 1.5.0
	 *
	 * @var string
	 */
	const TABLE_NAME = 'webhook_subscriptions';

	/**
	 * Latest DB schema version.
	 *
	 * @since 1.5.0
	 *
	 * @var int
	 */
	const LATEST_VERSION = 1;

	/**
	 * Create the table.
	 *
	 * @since 1.5.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,
			connection_id BIGINT UNSIGNED NOT NULL,
			provider VARCHAR(50) NOT NULL,
			resource_id VARCHAR(255) NOT NULL DEFAULT '',
			subscription_id VARCHAR(255) NOT NULL,
			expires_at DATETIME NULL,
			status VARCHAR(20) NOT NULL DEFAULT 'active',
			created_at DATETIME NOT NULL,
			updated_at DATETIME NOT NULL,
			PRIMARY KEY (id),
			UNIQUE KEY uq_connection_resource (connection_id, resource_id),
			INDEX idx_subscription_id (subscription_id),
			INDEX idx_connection (connection_id)
		) {$collate};";

		dbDelta( $sql );

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