<?php
/**
 * File Path: includes/migrations/class-mbs-migration-4-27.php
 *
 * Migration 4.27 -- R2-003: Bowker Codes Post-Meta to DB Table
 *
 * Backfills existing mbs_bowker_codes JSON post_meta into
 * mbs_book_classification_codes with scheme='bowker'. The code path
 * switches to MBS_Classification_DAO::save_book_codes/get_book_codes
 * in the same sprint. Post-meta rows are left in place for one sprint
 * as a rollback safety net and can be deleted by a follow-up migration.
 *
 * Idempotency: deletes any existing bowker rows for each post before
 * reinserting, so the migration can be safely re-run.
 *
 * @package MyBookShowroom
 * @since   1.2.0
 */

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

/**
 * Migration 4.27: Backfill Bowker codes from post_meta to mbs_book_classification_codes.
 *
 * @implements MBS_Migration
 */
class MBS_Migration_4_27 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.2.0
	 */
	public function version(): string {
		return '4.27';
	}

	/**
	 * Backfill mbs_bowker_codes post_meta into mbs_book_classification_codes.
	 *
	 * Reads every post that has a non-empty mbs_bowker_codes post_meta value,
	 * json_decodes the stored array, and inserts each code into the
	 * mbs_book_classification_codes table with scheme='bowker'.
	 *
	 * @return void
	 * @since 1.2.0
	 */
	public function up(): void {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_book_classification_codes';

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			"SELECT post_id, meta_value FROM {$wpdb->postmeta}
			 WHERE meta_key = 'mbs_bowker_codes'
			   AND meta_value != ''
			   AND meta_value != '[]'",
			ARRAY_A
		);

		if ( empty( $rows ) ) {
			return;
		}

		foreach ( $rows as $row ) {
			$post_id = (int) $row['post_id'];
			$codes   = json_decode( (string) $row['meta_value'], true );

			if ( ! is_array( $codes ) || empty( $codes ) ) {
				continue;
			}

			// Idempotent: clear any existing bowker rows for this post first.
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$wpdb->delete( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
				$table,
				array( 'post_id' => $post_id, 'scheme' => 'bowker' ),
				array( '%d', '%s' )
			);

			foreach ( array_values( $codes ) as $sort_order => $code ) {
				$code = sanitize_text_field( (string) $code );
				if ( '' === $code ) {
					continue;
				}
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
					$table,
					array(
						'post_id'    => $post_id,
						'scheme'     => 'bowker',
						'code'       => $code,
						'sort_order' => $sort_order,
					),
					array( '%d', '%s', '%s', '%d' )
				);
			}
		}
	}
}
