<?php
/**
 * File Path: includes/migrations/class-mbs-migration-4-6.php
 *
 * MBS_Migration_4_6 -- Schema 4.6 -- Phase 1: Book Classification Codes Table
 *
 * Creates the mbs_book_classification_codes table and backfills it from
 * existing mbs_bisac_codes, mbs_thema_codes, mbs_bic_codes, and
 * mbs_illustration_types postmeta JSON arrays. All operations are
 * idempotent: the SHOW TABLES guard prevents re-creation; backfill uses
 * INSERT IGNORE on the UNIQUE KEY (post_id, scheme, code).
 *
 * The postmeta keys are NOT deleted here — dual-write continues through
 * the compatibility period; cleanup is deferred to migration 4.7.
 *
 * Valid scheme values: bisac | thema | bic | illustration | bowker
 *
 * @package MyBookShowroom
 * @since   4.6.0
 */

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

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

	/**
	 * {@inheritdoc}
	 *
	 * @return string
	 */
	public function version(): string {
		return '4.6';
	}

	/**
	 * {@inheritdoc}
	 *
	 * Creates mbs_book_classification_codes and backfills from postmeta.
	 *
	 * @return void
	 */
	public function up(): void {
		global $wpdb;

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

		// Idempotency guard: skip if table already exists.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
		$exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) );
		if ( $exists ) {
			return;
		}

		// ---- 1. Create 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 IF NOT EXISTS `{$table}` (
				`id`         BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
				`post_id`    BIGINT UNSIGNED  NOT NULL,
				`scheme`     VARCHAR(20)      NOT NULL,
				`code`       VARCHAR(50)      NOT NULL,
				`sort_order` TINYINT UNSIGNED NOT NULL DEFAULT 0,
				PRIMARY KEY (`id`),
				UNIQUE KEY `idx_post_scheme_code` (`post_id`, `scheme`, `code`),
				KEY `idx_post_id` (`post_id`),
				KEY `idx_scheme_code` (`scheme`, `code`)
			) {$charset_collate}"
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.SchemaChange

		// ---- 2. Backfill from postmeta -----------------------------------------
		// Schemes and the postmeta keys that contain their JSON arrays.
		$scheme_map = array(
			'mbs_bisac_codes'        => 'bisac',
			'mbs_thema_codes'        => 'thema',
			'mbs_bic_codes'          => 'bic',
			'mbs_illustration_types' => 'illustration',
		);

		foreach ( $scheme_map as $meta_key => $scheme ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT post_id, meta_value FROM {$wpdb->postmeta}
					 WHERE meta_key = %s
					   AND meta_value != ''
					   AND meta_value != 'null'
					   AND meta_value != '[]'",
					$meta_key
				),
				ARRAY_A
			);
			if ( empty( $rows ) ) {
				continue;
			}
			foreach ( $rows as $row ) {
				$codes = json_decode( (string) $row['meta_value'], true );
				if ( ! is_array( $codes ) ) {
					continue;
				}
				foreach ( array_values( $codes ) as $order => $code ) {
					$code = (string) $code;
					if ( '' === $code ) {
						continue;
					}
					// INSERT IGNORE is idempotent on the UNIQUE KEY.
					// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
					$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
						$wpdb->prepare(
							"INSERT IGNORE INTO `{$table}` (post_id, scheme, code, sort_order)
							 VALUES (%d, %s, %s, %d)",
							(int) $row['post_id'],
							$scheme,
							$code,
							$order
						)
					);
				}
			}
		}
	}
}
