<?php
/**
 * File Path: includes/migrations/class-mbs-migration-4-7.php
 *
 * Migration 4.7 — mbs_book_awards table + Phase 1 classification postmeta cleanup
 *
 * DDL: CREATE TABLE mbs_book_awards (id, post_id, title, award_year, status, sort_order).
 * Backfills from mbs_awards postmeta JSON (one row per award entry).
 * Also deletes orphan postmeta rows for the four classification keys retired in
 * Phase 1 (mbs_bisac_codes, mbs_thema_codes, mbs_bic_codes, mbs_illustration_types).
 *
 * @package MyBookShowroom
 * @since   1.6.0
 */

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

/**
 * Migration 4.7: mbs_book_awards table + Phase 1 classification postmeta cleanup.
 */
// 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_7 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.7';
	}

	/**
	 * 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_awards';
		$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} (
					id         BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
					post_id    BIGINT UNSIGNED  NOT NULL,
					title      VARCHAR(255)     NOT NULL DEFAULT '',
					award_year YEAR             NULL DEFAULT NULL,
					status     VARCHAR(20)      NOT NULL DEFAULT 'finalist',
					sort_order TINYINT UNSIGNED NOT NULL DEFAULT 0,
					PRIMARY KEY (id),
					KEY idx_post_id (post_id)
				) {$charset_collate}"
			);
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.SchemaChange
		}

		// --- Backfill: decode mbs_awards postmeta JSON -----------------------
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$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_awards' AND meta_value != ''",
			ARRAY_A
		);

		foreach ( (array) $rows as $row ) {
			$post_id  = (int) $row['post_id'];
			$awards   = json_decode( (string) $row['meta_value'], true );
			if ( ! is_array( $awards ) || empty( $awards ) ) {
				continue;
			}
			foreach ( array_values( $awards ) as $sort_order => $award ) {
				$title      = sanitize_text_field( (string) ( $award['name']   ?? '' ) );
				$year_raw   = isset( $award['year'] ) && '' !== (string) $award['year']
				              ? (int) $award['year'] : null;
				$status     = sanitize_key( (string) ( $award['status'] ?? 'finalist' ) );
				if ( '' === $title ) {
					continue;
				}
				if ( null !== $year_raw ) {
					$wpdb->query( // 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.PreparedSQL.InterpolatedNotPrepared
							"INSERT IGNORE INTO {$table}
							 (post_id, title, award_year, status, sort_order)
							 VALUES (%d, %s, %d, %s, %d)",
							$post_id, $title, $year_raw, $status, $sort_order
						)
					);
				} else {
					$wpdb->query( // 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.PreparedSQL.InterpolatedNotPrepared
							"INSERT IGNORE INTO {$table}
							 (post_id, title, award_year, status, sort_order)
							 VALUES (%d, %s, NULL, %s, %d)",
							$post_id, $title, $status, $sort_order
						)
					);
				}
			}
		}

		// --- Phase 1 cleanup: delete classification postmeta orphans ----------
		// Readers were migrated to mbs_book_classification_codes in Phase 1.
		foreach ( array( 'mbs_bisac_codes', 'mbs_thema_codes', 'mbs_bic_codes', 'mbs_illustration_types' ) as $key ) {
			$wpdb->delete( $wpdb->postmeta, array( 'meta_key' => $key ), array( '%s' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.SlowDBQuery.slow_db_query_meta_key -- DAO layer -- direct DB access is intentional per MBS architecture
		}
	}
}
