<?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

		// AUD-007/008: dbDelta is the single rebuild authority. Create every table
		// here so a fresh install no longer depends on migrations (maybe_migrate()
		// is skipped outside admin/manage_options context, e.g. tests).
		// Previously-unwired schema methods (AUD-007):
		dbDelta( MBS_DB_Schema::pa_api_cache(       $wpdb->prefix . 'mbs_pa_api_cache',       $cc ) );
		// NOTE: MBS_DB_Schema::contributors() is intentionally NOT wired — the legacy
		// mbs_contributors table is dropped by migration 4-14 (replaced by
		// mbs_book_contributors). Creating it here would just be undone by run_migrations().
		dbDelta( MBS_DB_Schema::book_contributors(  $wpdb->prefix . 'mbs_book_contributors',  $cc ) );
		dbDelta( MBS_DB_Schema::related_products(   $wpdb->prefix . 'mbs_related_products',   $cc ) );
		dbDelta( MBS_DB_Schema::pos_locations(      $wpdb->prefix . 'mbs_pos_locations',      $cc ) );
		dbDelta( MBS_DB_Schema::pos_inventory(      $wpdb->prefix . 'mbs_pos_inventory',      $cc ) );
		dbDelta( MBS_DB_Schema::buy_link_clicks(    $wpdb->prefix . 'mbs_buy_link_clicks',    $cc ) );
		dbDelta( MBS_DB_Schema::brand_feed_cache(   $wpdb->prefix . 'mbs_brand_feed_cache',   $cc ) );
		// Migration-only / seeder-only tables, now canonical (AUD-008):
		dbDelta( MBS_DB_Schema::book_meta(                $wpdb->prefix . 'mbs_book_meta',                $cc ) );
		dbDelta( MBS_DB_Schema::book_stats(               $wpdb->prefix . 'mbs_book_stats',               $cc ) );
		dbDelta( MBS_DB_Schema::book_awards(              $wpdb->prefix . 'mbs_book_awards',              $cc ) );
		dbDelta( MBS_DB_Schema::arc_campaigns(            $wpdb->prefix . 'mbs_arc_campaigns',            $cc ) );
		dbDelta( MBS_DB_Schema::book_territorial_rights(  $wpdb->prefix . 'mbs_book_territorial_rights',  $cc ) );
		dbDelta( MBS_DB_Schema::book_classification_codes($wpdb->prefix . 'mbs_book_classification_codes', $cc ) );
		dbDelta( MBS_DB_Schema::classification_codes(     $wpdb->prefix . 'mbs_classification_codes',     $cc ) );
		dbDelta( MBS_DB_Schema::bisac_thema_map(          $wpdb->prefix . 'mbs_bisac_thema_map',          $cc ) );
		dbDelta( MBS_DB_Schema::bic_thema_map(            $wpdb->prefix . 'mbs_bic_thema_map',            $cc ) );

		// Run every migration unconditionally so a fresh install / repair applies
		// all CREATE/ALTER steps even outside an admin request (tests, WP-CLI).
		self::run_migrations();
	}

	/**
	 * 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/003: the admin_init hook path only runs migrations for admins.
		// install()/repair() call run_migrations() directly (unconditional).
		if ( ! is_admin() || ! current_user_can( 'manage_options' ) ) {
			return;
		}
		self::run_migrations();
	}

	/**
	 * Run all pending migrations unconditionally (no capability/context guard).
	 *
	 * Called by install()/repair() so a fresh install or table rebuild applies
	 * every migration's CREATE/ALTER even outside an admin request (e.g. tests,
	 * WP-CLI). The admin_init hook path goes through maybe_migrate() instead.
	 *
	 * @return void
	 * @since 1.3.9
	 */
	public static function run_migrations(): void {
		$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.
		global $wpdb;
		foreach ( self::get_migrations() as $migration ) {
			if ( version_compare( $current, $migration->version(), '<' ) ) {
				try {
					$wpdb->last_error = ''; // AUD-006: clear so a silent SQL failure in up() is detectable.
					$migration->up();
					// AUD-006: $wpdb->query() returns false (does NOT throw) on a SQL error.
					// Surface it as an exception so the catch below halts instead of
					// stamping a failed migration as complete.
					if ( '' !== $wpdb->last_error ) {
						throw new \RuntimeException( 'SQL error: ' . $wpdb->last_error );
					}
					// Checkpoint: stamp version after each verified-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, missing: list<string>, version: string }
	 */
	public static function repair(): array {
		delete_option( 'mbs_db_version' );
		self::install();
		// Heal: repair() must also (re-)create + repopulate the classification
		// reference tables, which install()/maybe_migrate() do not touch.
		MBS_Classification_Seeder::seed();
		global $wpdb;

		// AUD-015: verify ground truth rather than trusting only the last query's error.
		$required = array(
			'mbs_format_rows', 'mbs_buy_links', 'mbs_endorsements', 'mbs_section_order',
			'mbs_arc_recipients', 'mbs_arc_applicants', 'mbs_arc_review_urls', 'mbs_reading_order',
			'mbs_book_collection_positions', 'mbs_book_club', 'mbs_book_club_questions',
			'mbs_book_club_files', 'mbs_activity_log', 'mbs_esp_queue', 'mbs_affiliate_stores',
			'mbs_author_social_links', 'mbs_brand_feed_urls',
			// migration-only + seeder tables (see AUD-007/008):
			'mbs_book_meta', 'mbs_book_stats', 'mbs_book_classification_codes', 'mbs_book_awards',
			'mbs_arc_campaigns', 'mbs_book_territorial_rights',
			'mbs_classification_codes', 'mbs_bisac_thema_map', 'mbs_bic_thema_map',
		);
		$missing = array();
		foreach ( $required as $t ) {
			$full = $wpdb->prefix . $t;
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $full ) ) !== $full ) {
				$missing[] = $t;
			}
		}

		return array(
			'success' => empty( $missing ),
			'missing' => $missing,
			'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 {
			$missing = isset( $result['missing'] ) ? (array) $result['missing'] : array();
			$message = empty( $missing )
				? __( 'Repair completed with errors. Check your PHP error log for details.', 'my-book-showroom' )
				: sprintf(
					/* translators: %s: comma-separated list of missing database table names */
					__( 'Repair completed with errors. Missing tables: %s', 'my-book-showroom' ),
					esc_html( implode( ', ', $missing ) )
				);
			wp_send_json_error( array( 'message' => $message ) );
		}
	}

	/**
	 * 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
	}
}
