<?php
/**
 * File Path: includes/class-mbs-db-installer.php
 *
 * Database Installer and Migrator
 *
 * Creates MBS custom tables on activation and runs versioned migrations
 * on every admin request via maybe_migrate().
 *
 * To add a new migration: create includes/migrations/class-mbs-migration-X-X.php,
 * implement MBS_Migration, then append a new instance to get_migrations().
 *
 * @package MyBookShowroom
 * @since   1.0.0
 */

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

// 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_DB_Installer {

	/**
	 * Create all MBS custom tables and run any pending migrations.
	 *
	 * Called on plugin activation. Uses dbDelta() for idempotent schema creation.
	 * Delegates version tracking to maybe_migrate() so fresh installs run through
	 * every migration block before the stored mbs_db_version is advanced.
	 *
	 * @return void
	 */
	public static function install(): void {
		global $wpdb;

		$cc = $wpdb->get_charset_collate();

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		dbDelta( MBS_DB_Schema::format_rows(    $wpdb->prefix . 'mbs_format_rows',    $cc ) );
		dbDelta( MBS_DB_Schema::buy_links(      $wpdb->prefix . 'mbs_buy_links',      $cc ) );
		dbDelta( MBS_DB_Schema::endorsements(   $wpdb->prefix . 'mbs_endorsements',   $cc ) );
		dbDelta( MBS_DB_Schema::section_order(  $wpdb->prefix . 'mbs_section_order',  $cc ) );
		dbDelta( MBS_DB_Schema::arc_recipients( $wpdb->prefix . 'mbs_arc_recipients', $cc ) );
		dbDelta( MBS_DB_Schema::arc_applicants( $wpdb->prefix . 'mbs_arc_applicants', $cc ) );
		dbDelta( MBS_DB_Schema::arc_review_urls(     $wpdb->prefix . 'mbs_arc_review_urls',     $cc ) ); // R2-004
		dbDelta( MBS_DB_Schema::reading_order(       $wpdb->prefix . 'mbs_reading_order',         $cc ) ); // R2-002
		dbDelta( MBS_DB_Schema::book_collection_positions( $wpdb->prefix . 'mbs_book_collection_positions', $cc ) );
		dbDelta( MBS_DB_Schema::book_club(           $wpdb->prefix . 'mbs_book_club',           $cc ) );
		dbDelta( MBS_DB_Schema::book_club_questions( $wpdb->prefix . 'mbs_book_club_questions', $cc ) );
		dbDelta( MBS_DB_Schema::book_club_files(     $wpdb->prefix . 'mbs_book_club_files',     $cc ) );
		dbDelta( MBS_DB_Schema::activity_log(   $wpdb->prefix . 'mbs_activity_log',   $cc ) );
		dbDelta( MBS_DB_Schema::esp_queue(          $wpdb->prefix . 'mbs_esp_queue',          $cc ) ); // RF-011
		dbDelta( MBS_DB_Schema::affiliate_stores(   $wpdb->prefix . 'mbs_affiliate_stores',   $cc ) ); // RF-012
		dbDelta( MBS_DB_Schema::author_social_links( $wpdb->prefix . 'mbs_author_social_links', $cc ) ); // RF-012
		dbDelta( MBS_DB_Schema::brand_feed_urls(    $wpdb->prefix . 'mbs_brand_feed_urls',    $cc ) ); // RF-012

		// Delegate version tracking to maybe_migrate() so upgrade paths that
		// run through install() still execute their version-gated migration
		// objects before the stored mbs_db_version is advanced.
		self::maybe_migrate();
	}

	/**
	 * Seed the global default section order if no rows exist for post_id = 0.
	 *
	 * Idempotent — returns early if the global default is already present.
	 * Called at the end of every successful migration run.
	 *
	 * @return void
	 */
	public static function seed_default_section_order(): void {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_section_order';

		// Single canonical source (UX-EDIT-002 / Phase 0-B): import from helper
		// so there is exactly one hardcoded copy of the section key list.
		$default_order = MBS_Section_Helper::get_canonical_keys();

		// Fetch existing section keys so we can skip those and append only new ones.
		// This makes the seed idempotent for existing installs (CE-1: awards-accolades
		// is appended at max_sort_order+1 so it does not displace existing ordering).
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- no user input
		$existing = $wpdb->get_col( "SELECT section_key FROM {$table} WHERE post_id = 0" ); // 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 -- no user input
		$max_order = (int) $wpdb->get_var( "SELECT COALESCE(MAX(sort_order),0) FROM {$table} WHERE post_id = 0" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture

		foreach ( $default_order as $i => $section ) {
			if ( in_array( $section, $existing, true ) ) {
				continue; // Already seeded -- do not overwrite.
			}
			$sort = empty( $existing ) ? ( $i + 1 ) : ( ++$max_order );
			$wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
				$table,
				array(
					'post_id'     => 0,
					'section_key' => $section,
					'sort_order'  => $sort,
				)
			);
		}
	}

	/**
	 * Run any pending database migrations.
	 *
	 * Compares the stored mbs_db_version option against MBS_DB_VERSION.
	 * Iterates get_migrations() in ascending version order and calls up()
	 * on each migration whose version is greater than the stored value.
	 *
	 * After all migrations run:
	 *  - Ensures the global default section order rows exist (idempotent).
	 *  - Cleans up the orphaned mbs_license_data option (CODE-01).
	 *  - Advances mbs_db_version to MBS_DB_VERSION.
	 *
	 * @return void
	 */
	public static function maybe_migrate(): void {
		// V5-D-002: migration must run in admin context only.
		if ( ! is_admin() ) {
			return;
		}
		// V5-D-003: only users who can manage options may trigger migrations.
		if ( ! current_user_can( 'manage_options' ) ) {
			return;
		}

		$current = get_option( 'mbs_db_version', '0' );
		if ( ! version_compare( $current, MBS_DB_VERSION, '<' ) ) {
			return;
		}

		// V5-D-004/005: per-migration checkpoint + try/catch with partial-failure halt.
		foreach ( self::get_migrations() as $migration ) {
			if ( version_compare( $current, $migration->version(), '<' ) ) {
				try {
					$migration->up();
					// Checkpoint: stamp version after each successful migration.
					update_option( 'mbs_db_version', $migration->version(), false );
					$current = $migration->version(); // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_trigger_error -- trigger_error() used for recoverable errors in plugin lifecycle; acceptable in non-production contexts and gated by MBS_DEV_MODE
				} catch ( \Throwable $e ) {
					trigger_error( // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_trigger_error, WordPress.Security.EscapeOutput.OutputNotEscaped -- trigger_error() in plugin lifecycle; acceptable for recoverable errors
						sprintf( 'MBS migration %s failed: %s', $migration->version(), $e->getMessage() ), // phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped -- trigger_error message; never rendered as HTML
						E_USER_WARNING
					);
					return; // Halt on first failure — do not advance further.
				}
			}
		}

		// Ensure global default section order rows exist (idempotent).
		self::seed_default_section_order();
		// CODE-01: Remove the orphaned mbs_license_data option.
		delete_option( 'mbs_license_data' );

		// V6-B-012: final stamp with autoload=false (consistent with checkpoints above).
		update_option( 'mbs_db_version', MBS_DB_VERSION, false );
	}

	/**
	 * Returns all registered database migrations in ascending version order.
	 *
	 * Add a new migration by creating
	 * includes/migrations/class-mbs-migration-X-X.php, implementing MBS_Migration,
	 * and appending a new instance here.
	 *
	 * @return MBS_Migration[]
	 */
	private static function get_migrations(): array {
		return array(
			new MBS_Migration_1_3(),
			new MBS_Migration_1_4(),
			new MBS_Migration_1_5(),
			new MBS_Migration_1_6(),
			new MBS_Migration_1_7(),
			new MBS_Migration_1_8(),
			new MBS_Migration_1_9(),
			new MBS_Migration_2_0(),
			new MBS_Migration_2_1(),
			new MBS_Migration_2_2(),
			new MBS_Migration_2_3(),
			new MBS_Migration_2_4(),
			new MBS_Migration_2_5(),
			new MBS_Migration_2_6(),
			new MBS_Migration_2_7(),
			new MBS_Migration_2_8(),
			new MBS_Migration_2_9(),
			new MBS_Migration_3_0(),
			new MBS_Migration_3_1(),
			new MBS_Migration_3_2(),
			new MBS_Migration_3_3(),
			new MBS_Migration_3_4(),
			new MBS_Migration_3_5(),
			new MBS_Migration_3_6(),
			new MBS_Migration_3_7(),
			new MBS_Migration_3_8(),
			new MBS_Migration_3_9(),
			new MBS_Migration_4_0(),
			new MBS_Migration_4_1(),
			new MBS_Migration_4_2(),
			new MBS_Migration_4_3(),
			new MBS_Migration_4_4(),
			new MBS_Migration_4_5(),
			new MBS_Migration_4_6(),
			new MBS_Migration_4_7(),
				new MBS_Migration_4_8(),
				new MBS_Migration_4_9(),
				new MBS_Migration_4_10(),
				new MBS_Migration_4_11(),
				new MBS_Migration_4_12(),
				new MBS_Migration_4_13(),
				new MBS_Migration_4_14(),
				new MBS_Migration_4_15(),
				new MBS_Migration_4_16(),
				new MBS_Migration_4_17(),
				new MBS_Migration_4_18(),
				new MBS_Migration_4_19(),
				new MBS_Migration_4_20(),
				new MBS_Migration_4_21(),
				new MBS_Migration_4_22(),
				new MBS_Migration_4_23(),
				new MBS_Migration_4_24(),
				new MBS_Migration_4_25(),
				new MBS_Migration_4_26(),
				new MBS_Migration_4_27(), // R2-003: Bowker codes post_meta → mbs_book_classification_codes
				new MBS_Migration_4_28(), // R2-004: mbs_arc_review_urls child table
				new MBS_Migration_4_29(), // R2-002: mbs_reading_order junction table
				new MBS_Migration_4_30(), // UX-EDIT-002: remove social-share/suggested-r-o stale rows + re-seed
				new MBS_Migration_4_31(), // UX-EDIT-002 follow-up: reset global default sort_order to canonical order
				new MBS_Migration_4_32(), // UX-NAV-001: is_visible column + stale key deletion
				new MBS_Migration_4_33(), // Session 212: correct global default section display order
				new MBS_Migration_4_34(), // Sprint RO-Display: series_reading_order_position + suppress columns
				new MBS_Migration_4_35(), // Sprint RO-Display: retire mbs_reading_order type series + collection rows
				new MBS_Migration_4_36(), // Sprint RO-Display: hero term_meta cleanup
				new MBS_Migration_4_37(), // UX-RO-002: ADD COLUMN universe_reading_order_position to mbs_book_meta
				new MBS_Migration_4_38(), // DB index optimization: drop 4 redundant indexes; normalize mbs_book_meta.isbn13 to VARCHAR(17)
				new MBS_Migration_4_39(), // Session 224 -- add series_number DECIMAL(5,2) column to mbs_format_rows
				new MBS_Migration_4_40(), // Session 238 -- widen returnability VARCHAR(2) -> VARCHAR(3); fixes silent INSERT failure for yes-valued rows
				new MBS_Migration_4_41(), // DAT-022: add KEY idx_post_id to mbs_book_awards if missing
				new MBS_Migration_4_42(), // DAT-022b: add KEY idx_post_id to mbs_book_club_files if missing
				new MBS_Migration_4_43(), // DAT-003 / V2-D-015: add book_title column to mbs_esp_queue; backfill list_id
		);
	}

	/**
	 * Register the section order warning admin notice hook.
	 *
	 * @return void
	 * @since 1.3.5
	 */
	public static function register_hooks(): void {
		add_action( 'admin_notices',               array( __CLASS__, 'show_section_order_warning' ) );
		add_action( 'wp_ajax_mbs_repair_tables',   array( __CLASS__, 'ajax_repair' ) );
	}

	/**
	 * Force-recreate all MBS database tables and re-run every migration.
	 *
	 * Clears the stored schema version so maybe_migrate() starts from scratch,
	 * then calls install() which runs dbDelta() on all base tables and calls
	 * maybe_migrate() to execute every migration in sequence.
	 *
	 * @return array{ success: bool, version: string }
	 */
	public static function repair(): array {
		delete_option( 'mbs_db_version' );
		self::install();
		global $wpdb;
		$last_error = (string) $wpdb->last_error;
		return array(
			'success' => '' === $last_error,
			'version' => (string) get_option( 'mbs_db_version', '0' ),
		);
	}

	/**
	 * AJAX handler for the Settings -> Advanced -> Repair Tables button.
	 *
	 * Validates nonce and manage_options capability, delegates to repair(),
	 * and returns a JSON response.
	 *
	 * @return void
	 */
	public static function ajax_repair(): void {
		check_ajax_referer( 'mbs_repair_tables', 'nonce' );
		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error(
				array( 'message' => __( 'Insufficient permissions.', 'my-book-showroom' ) ),
				403
			);
		}
		$result = self::repair();
		if ( $result['success'] ) {
			wp_send_json_success(
				array(
					/* translators: %s: database schema version number */
					'message' => sprintf( __( 'Database tables repaired. Schema version: %s', 'my-book-showroom' ), esc_html( $result['version'] ) ),
				)
			);
		} else {
			wp_send_json_error(
				array( 'message' => __( 'Repair completed with errors. Check your PHP error log for details.', 'my-book-showroom' ) )
			);
		}
	}

	/**
	 * Surface the D-007 diagnostic flag as an admin notice.
	 *
	 * Fires on every admin page. When mbs_section_order_warning is set,
	 * the global default section order rows are missing and the plugin
	 * cannot render the book page correctly.
	 * MBS_DB_Installer::seed_default_section_order() clears this flag
	 * on the next activation or migration run.
	 *
	 * @return void
	 */
	public static function show_section_order_warning(): void {
		if ( 'global_defaults_missing' !== get_option( 'mbs_section_order_warning' ) ) {
			return;
		}
		?>
		<div class="notice notice-error">
			<p>
				<strong><?php esc_html_e( 'My Book Showroom:', 'my-book-showroom' ); ?></strong>
				<?php esc_html_e( 'Section order defaults are missing. Deactivate and reactivate the plugin to restore them.', 'my-book-showroom' ); ?>
			</p>
		</div>
		<?php
	}
}
