<?php
/**
 * File Path: includes/migrations/class-mbs-migration-2-4.php
 *
 * MBS_Migration_2_4 — Sprint F-020 — Migrate contributors to book_contributors
 *
 * Create mbs_book_contributors table, migrate rows from mbs_contributors with
 * ONIX role code mapping, and drop the old table once counts match.
 *
 * @package MyBookShowroom
 * @since   3.2.0
 */

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

/**
 * Class MBS_Migration_2_4
 *
 * Create mbs_book_contributors table, migrate rows from mbs_contributors with
 * ONIX role code mapping, and drop the old table once counts match.
 *
 * @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_2_4 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 '2.4';
	}

	/**
	 * {@inheritdoc}
	 *
	 * Create mbs_book_contributors table, migrate rows from mbs_contributors with
 * ONIX role code mapping, and drop the old table once counts match.
	 *
	 * @return void
	 */
	public function up(): void {
		global $wpdb;
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		$charset_collate = $wpdb->get_charset_collate();
		dbDelta( MBS_DB_Schema::book_contributors( $wpdb->prefix . 'mbs_book_contributors', $charset_collate ) );

		// Temporarily register mbs_contributor so term functions are available
		// during this migration (maybe_migrate() runs before the init hook).
		if ( ! taxonomy_exists( 'mbs_contributor' ) ) {
			register_taxonomy( 'mbs_contributor', MBS_POST_TYPE, array( 'public' => false, 'show_ui' => false, 'rewrite' => false ) );
		}

		$old_table = $wpdb->prefix . 'mbs_contributors';
		$new_table = $wpdb->prefix . 'mbs_book_contributors';
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$table_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $old_table ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
		if ( $table_exists ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			$old_rows = $wpdb->get_results( "SELECT * FROM {$old_table} WHERE deleted_at IS NULL", ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			$role_map = array(
				'author'       => 'A01',
				'illustrator'  => 'A12',
				'photographer' => 'A13',
				'editor'       => 'B01',
				'translator'   => 'B06',
			);
			foreach ( $old_rows as $row ) {
				$name = trim( $row['name'] );
				if ( empty( $name ) ) { continue; }
				$term = term_exists( $name, 'mbs_contributor' );
				if ( ! $term ) {
					$term = wp_insert_term( $name, 'mbs_contributor' );
				}
				if ( is_wp_error( $term ) || ! isset( $term['term_id'] ) ) { continue; }
				$role_lower = strtolower( trim( $row['role'] ) );
				$onix_code  = isset( $role_map[ $role_lower ] ) ? $role_map[ $role_lower ] : 'A01';
				$wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
					$new_table,
					array(
						'post_id'        => (int) $row['post_id'],
						'term_id'        => (int) $term['term_id'],
						'onix_role_code' => $onix_code,
						'role_label'     => sanitize_text_field( $row['role'] ),
						'sort_order'     => (int) $row['sort_order'],
						'public'         => (int) $row['public'],
						'statement'      => null,
						'deleted_at'     => null,
					),
					array( '%d', '%d', '%s', '%s', '%d', '%d', '%s', '%s' )
				);
			}
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			$old_count = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$old_table} WHERE deleted_at IS NULL" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			$new_count = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$new_table}" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			if ( $old_count === $new_count ) {
				// phpcs:ignore WordPress.DB.PreparedSQL.SchemaChange,WordPress.DB.PreparedSQL.NotPrepared
				$wpdb->query( "DROP TABLE IF EXISTS {$old_table}" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- DAO layer -- direct DB access is intentional per MBS architecture
			}
		}
	}
}
