<?php
/**
 * File Path: includes/migrations/class-mbs-migration-4-23.php
 *
 * Migration 4.23 -- Migrate author social links from term meta JSON blob
 * to the mbs_author_social_links table (RF-012).
 *
 * Source: term meta key 'mbs_author_social_links' on every term of
 *         taxonomy MBS_TAX_AUTHOR.
 * Each record carries: platform, url[, label].
 *   label is stored in the old format but has no column in the new table
 *   (it was never surfaced in the UI); it is silently dropped.
 *
 * sort_order is assigned by position within each author's array.
 * created_at / updated_at set to current UTC time on insert.
 *
 * Idempotency: per-author guard -- if any row exists for a given term_id,
 * that author's links are skipped. This allows partial-run replay.
 *
 * @package MyBookShowroom
 * @since   1.3.0
 */

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

/**
 * Migration 4.23: Author social links term meta -> mbs_author_social_links 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_23 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.23';
	}

	/**
	 * Run the migration.
	 *
	 * Iterates every term in the author taxonomy, reads the
	 * mbs_author_social_links term meta (JSON), and inserts rows into
	 * the mbs_author_social_links table.
	 *
	 * Skips individual authors whose rows already exist (per-term idempotency).
	 *
	 * @return void
	 * @since 1.3.0
	 */
	public function up(): void {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_author_social_links';
		$now   = current_time( 'mysql', true ); // UTC (A-005).

		$terms = get_terms(
			array(
				'taxonomy'   => MBS_TAX_AUTHOR,
				'hide_empty' => false,
				'fields'     => 'ids',
			)
		);

		if ( is_wp_error( $terms ) || empty( $terms ) ) {
			return;
		}

		foreach ( $terms as $term_id ) {
			$term_id = (int) $term_id;

			// Per-term idempotency guard.
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$existing = (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
				$wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE term_id = %d", $term_id )
			);
			if ( $existing > 0 ) {
				continue;
			}

			$raw = get_term_meta( $term_id, 'mbs_author_social_links', true );
			if ( empty( $raw ) ) {
				continue;
			}

			$links = is_array( $raw ) ? $raw : json_decode( $raw, true );
			if ( ! is_array( $links ) || empty( $links ) ) {
				continue;
			}

			foreach ( $links as $i => $link ) {
				if ( ! is_array( $link ) ) {
					continue;
				}

				$platform = isset( $link['platform'] ) ? sanitize_key( $link['platform'] ) : '';
				$url      = isset( $link['url'] ) ? esc_url_raw( $link['url'] ) : '';

				if ( '' === $platform || '' === $url ) {
					continue;
				}

				$wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
					$table,
					array(
						'term_id'    => $term_id,
						'platform'   => $platform,
						'url'        => $url,
						'sort_order' => $i,
						'created_at' => $now,
						'updated_at' => $now,
					),
					array( '%d', '%s', '%s', '%d', '%s', '%s' )
				);
			}
		}
	}
}
