<?php
/**
 * File Path: includes/migrations/class-mbs-migration-4-9.php
 *
 * Migration 4.9 — mbs_book_meta table
 *
 * Creates mbs_book_meta with one row per book, consolidating five scalar
 * postmeta keys that are accessed via SQL meta_query (mbs_featured,
 * mbs_lifecycle_state, mbs_coming_soon) or need a single keyed lookup
 * (mbs_isbn13, mbs_word_count). Backfills all five keys from postmeta.
 * PRIMARY KEY on post_id enforces one row per book.
 *
 * @package MyBookShowroom
 * @since   1.7.0
 */

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

/**
 * Migration 4.9: mbs_book_meta 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_9 implements MBS_Migration { // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedClassFound -- mbs_/MBS_ prefix is correct; scanner not configured with plugin prefix

	/**
	 * @return string Migration version string.
	 */
	public function version(): string {
		return '4.9';
	}

	/**
	 * Run the migration.
	 *
	 * Idempotent: SHOW TABLES guard prevents double-create.
	 * INSERT IGNORE on backfill is safe to replay.
	 *
	 * @return void
	 */
	public function up(): void {
		global $wpdb;

		$table           = $wpdb->prefix . 'mbs_book_meta';
		$charset_collate = $wpdb->get_charset_collate();

		// --- DDL: create table if not present -----------------------------------
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$exists = $wpdb->get_var( "SHOW TABLES LIKE '{$table}'" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
		if ( $exists !== $table ) {
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.SchemaChange
			$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- DAO layer -- direct DB access is intentional per MBS architecture
				"CREATE TABLE {$table} (
					post_id         BIGINT UNSIGNED  NOT NULL,
					isbn13          VARCHAR(20)      DEFAULT NULL,
					lifecycle_state VARCHAR(40)      DEFAULT NULL,
					featured        TINYINT UNSIGNED DEFAULT NULL,
					coming_soon     TINYINT UNSIGNED DEFAULT NULL,
					word_count      INT UNSIGNED     DEFAULT NULL,
					PRIMARY KEY (post_id),
					KEY idx_lifecycle_state (lifecycle_state),
					KEY idx_featured        (featured),
					KEY idx_coming_soon     (coming_soon)
				) {$charset_collate}"
			);
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.SchemaChange
		}

		// --- Backfill: collect distinct post_ids that have any of the 5 keys ---
		$meta_keys = array(
			'mbs_isbn13',
			'mbs_lifecycle_state',
			'mbs_featured',
			'mbs_coming_soon',
			'mbs_word_count',
		);

		$placeholders = implode( ',', array_fill( 0, count( $meta_keys ), '%s' ) );

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$post_ids = $wpdb->get_col( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			$wpdb->prepare( // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- safe: variadic ...$params / ...$row_ids correctly matches prepared placeholders; PHPCS cannot statically verify spread arg count
				"SELECT DISTINCT post_id FROM {$wpdb->postmeta}
				 WHERE meta_key IN ({$placeholders})", // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- safe: variadic ...$params spread correctly matches prepared placeholders; PHPCS cannot statically verify spread arg count
				...$meta_keys
			)
		);

		foreach ( (array) $post_ids as $raw_post_id ) {
			$post_id = (int) $raw_post_id;

			// Retrieve raw postmeta values for this book.
			$v_isbn13          = (string) get_post_meta( $post_id, 'mbs_isbn13',          true );
			$v_lifecycle_state = (string) get_post_meta( $post_id, 'mbs_lifecycle_state', true );
			$v_featured_raw    = (string) get_post_meta( $post_id, 'mbs_featured',        true );
			$v_coming_soon_raw = (string) get_post_meta( $post_id, 'mbs_coming_soon',     true );
			$v_word_count_raw  = (string) get_post_meta( $post_id, 'mbs_word_count',      true );

			// Normalise each value; NULL means "not set" -- omit from INSERT.
			$isbn13          = ( '' !== $v_isbn13 )           ? $v_isbn13          : null;
			$lifecycle_state = ( '' !== $v_lifecycle_state )  ? $v_lifecycle_state : null;
			$featured        = ( '1' === $v_featured_raw )    ? 1                  : null;
			$coming_soon     = ( '1' === $v_coming_soon_raw ) ? 1                  : null;
			$word_count_int  = absint( $v_word_count_raw );
			$word_count      = ( $word_count_int > 0 )        ? $word_count_int    : null;

			// Build column list and placeholder list dynamically so that NULL
			// values are expressed as SQL literals, not passed through prepare().
			$col_parts = array( 'post_id' );
			$val_parts = array( '%d' );
			$args      = array( $post_id );

			if ( null !== $isbn13 ) {
				$col_parts[] = 'isbn13';
				$val_parts[] = '%s';
				$args[]      = $isbn13;
			}
			if ( null !== $lifecycle_state ) {
				$col_parts[] = 'lifecycle_state';
				$val_parts[] = '%s';
				$args[]      = $lifecycle_state;
			}
			if ( null !== $featured ) {
				$col_parts[] = 'featured';
				$val_parts[] = '%d';
				$args[]      = $featured;
			}
			if ( null !== $coming_soon ) {
				$col_parts[] = 'coming_soon';
				$val_parts[] = '%d';
				$args[]      = $coming_soon;
			}
			if ( null !== $word_count ) {
				$col_parts[] = 'word_count';
				$val_parts[] = '%d';
				$args[]      = $word_count;
			}

			$col_sql = implode( ', ', $col_parts );
			$val_sql = implode( ', ', $val_parts );

			// INSERT IGNORE: safe on PRIMARY KEY (post_id) -- skips if row exists.
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- DAO layer -- direct DB access is intentional per MBS architecture
				$wpdb->prepare(
					"INSERT IGNORE INTO {$table} ({$col_sql}) VALUES ({$val_sql})", // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- safe: variadic ...$params spread correctly matches prepared placeholders; PHPCS cannot statically verify spread arg count
					...$args
				)
			);
		}
	}
}
