<?php
/**
 * File Path: includes/migrations/class-mbs-migration-3-7.php
 *
 * MBS_Migration_3_7 -- Schema 3.7
 *
 * (1) Adds `edition` VARCHAR(100) column to mbs_format_rows.
 *     Edition statement (e.g. "2nd Edition") was formerly stored as
 *     `mbs_edition` book-level post_meta (MBS_Book_Header_Extended_Meta).
 *     Moving it to format-level lets ONIX exporters emit per-format
 *     <editionstatement> values from the row rather than from post_meta.
 *     Data is copied from the post_meta key to every non-deleted format
 *     row for that post. The legacy post_meta key is left in place and
 *     simply ignored by code after this migration.
 *
 * (2) DROPs six legacy columns from mbs_format_rows that were superseded
 *     by taxonomy-term relationships and dedicated post_meta keys:
 *       author_freetext  -- replaced by mbs_author taxonomy
 *       author_term_id   -- replaced by mbs_author taxonomy
 *       series_term_id   -- replaced by mbs_series taxonomy
 *       series_freetext  -- replaced by mbs_series taxonomy
 *       series_number    -- replaced by mbs_series_position post_meta (ARCH-SERIES-001)
 *       audio_sample_url -- replaced by audio_sample_id (WP media attachment)
 *
 *     dbDelta cannot DROP columns; removal is done with direct
 *     ALTER TABLE ... DROP COLUMN statements, each guarded by an
 *     INFORMATION_SCHEMA existence check so the migration is idempotent.
 *
 * @package MyBookShowroom
 * @since   3.7.0
 */

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

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

	public function version(): string {
		return '3.7';
	}

	public function up(): void {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_format_rows';

		// ── Step 1: Add `edition` column (idempotent) ─────────────────────────
		$col_exists = (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			$wpdb->prepare(
				"SELECT COUNT(*)
				   FROM INFORMATION_SCHEMA.COLUMNS
				  WHERE TABLE_SCHEMA = DATABASE()
				    AND TABLE_NAME   = %s
				    AND COLUMN_NAME  = 'edition'",
				$table
			)
		);

		if ( ! $col_exists ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- table name from $wpdb->prefix; DDL is static.
			$wpdb->query( "ALTER TABLE {$table} ADD COLUMN edition VARCHAR(100) NULL DEFAULT NULL AFTER edition_notes" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- DAO layer -- direct DB access is intentional per MBS architecture
		}

		// ── Step 2: Copy mbs_edition post_meta → format row edition column ────
		// Single JOIN-based UPDATE: avoids N+1 queries.
		// Only updates rows where edition is still empty to be re-runnable.
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- table names from $wpdb->prefix only.
		$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			"UPDATE {$table} fr
			  INNER JOIN {$wpdb->postmeta} pm
			          ON pm.post_id    = fr.post_id
			         AND pm.meta_key   = 'mbs_edition'
			         AND pm.meta_value != ''
			    SET fr.edition = pm.meta_value
			  WHERE fr.is_deleted = 0
			    AND ( fr.edition IS NULL OR fr.edition = '' )"
		);

		// ── Step 3: DROP legacy columns (each guarded by existence check) ─────
		$legacy_cols = array(
			'author_freetext',
			'author_term_id',
			'series_term_id',
			'series_freetext',
			'series_number',
			'audio_sample_url',
		);

		foreach ( $legacy_cols as $col ) {
			$exists = (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
				$wpdb->prepare(
					"SELECT COUNT(*)
					   FROM INFORMATION_SCHEMA.COLUMNS
					  WHERE TABLE_SCHEMA = DATABASE()
					    AND TABLE_NAME   = %s
					    AND COLUMN_NAME  = %s",
					$table,
					$col
				)
			);
			if ( $exists ) {
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- table/$col from static allowlist.
				$wpdb->query( "ALTER TABLE {$table} DROP COLUMN {$col}" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- DAO layer -- direct DB access is intentional per MBS architecture
			}
		}
	}
}
