<?php
/**
 * File Path: includes/migrations/class-mbs-migration-4-21.php
 *
 * Migration 4.21 -- Migrate ESP broadcast queue from wp_options ring-buffer
 * to the mbs_esp_queue table (RF-011).
 *
 * Source option: mbs_esp_broadcast_queue (QUEUE_KEY in MBS_ESP_Broadcast).
 * Each record carries: post_id, book_title, status, queued_at (Unix ts),
 * campaign_id, sent_at, platform.
 *
 * Mapping:
 *   provider   <- platform
 *   list_id    <- '' (not stored in old format)
 *   payload    <- full record JSON-encoded
 *   created_at <- gmdate('Y-m-d H:i:s', queued_at)  -- UTC
 *   sent_at    <- gmdate('Y-m-d H:i:s', sent_at) if set, else NULL
 *   status     <- status (values: pending|sent|failed)
 *
 * Idempotency: skips insert when mbs_esp_queue already contains rows.
 *
 * @package MyBookShowroom
 * @since   1.3.0
 */

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

/**
 * Migration 4.21: ESP broadcast queue wp_options -> mbs_esp_queue table.
 */
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- DAO/migration: table names via $wpdb->prefix are trusted server-controlled values, not user input
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter -- same reason: $wpdb->prefix table names are safe; $where_sql is built from sanitized server-controlled inputs
class MBS_Migration_4_21 implements MBS_Migration { // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedClassFound -- mbs_/MBS_ prefix is correct; scanner not configured with plugin prefix

	/**
	 * Return the migration version string.
	 *
	 * @return string
	 * @since 1.3.0
	 */
	public function version(): string {
		return '4.21';
	}

	/**
	 * Run the migration.
	 *
	 * Reads the mbs_esp_broadcast_queue option array and inserts each record
	 * into the mbs_esp_queue table. Skips the entire migration if the table
	 * already contains rows (idempotent replay guard).
	 *
	 * @return void
	 * @since 1.3.0
	 */
	public function up(): void {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_esp_queue';

		// Idempotency guard: skip if table already populated.
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$existing = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
		if ( $existing > 0 ) {
			return;
		}

		$queue = get_option( 'mbs_esp_broadcast_queue', array() );
		if ( ! is_array( $queue ) || empty( $queue ) ) {
			return;
		}

		foreach ( $queue as $record ) {
			if ( ! is_array( $record ) ) {
				continue;
			}

			$post_id    = isset( $record['post_id'] ) ? (int) $record['post_id'] : 0;
			$provider   = isset( $record['platform'] ) ? sanitize_key( $record['platform'] ) : '';
			$status     = isset( $record['status'] ) ? sanitize_key( $record['status'] ) : 'pending';
			$created_at = null;
			$sent_at    = null;

			if ( ! empty( $record['queued_at'] ) && is_numeric( $record['queued_at'] ) ) {
				$created_at = gmdate( 'Y-m-d H:i:s', (int) $record['queued_at'] );
			}
			if ( ! empty( $record['sent_at'] ) && is_numeric( $record['sent_at'] ) ) {
				$sent_at = gmdate( 'Y-m-d H:i:s', (int) $record['sent_at'] );
			}

			$wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
				$table,
				array(
					'post_id'    => $post_id,
					'provider'   => $provider,
					'list_id'    => '',
					'payload'    => wp_json_encode( $record ),
					'status'     => $status,
					'created_at' => $created_at,
					'sent_at'    => $sent_at,
				),
				array( '%d', '%s', '%s', '%s', '%s', '%s', '%s' )
			);
		}
	}
}
