<?php
/**
 * File Path: includes/data/class-mbs-classification-seeder.php
 *
 * Classification Seeder
 *
 * Creates the mbs_classification_codes, mbs_bisac_thema_map, and
 * mbs_bic_thema_map tables via raw SQL, then truncates and repopulates them
 * from the bundled JSON data files in data/ at the plugin root.
 *
 * Called on plugin activation and on every admin_init when the stored
 * mbs_classification_db_version option does not match DATA_VERSION.
 * DATA_VERSION is intentionally decoupled from MBS_VERSION so that bundled
 * JSON data files can be corrected without a plugin release bump.
 *
 * @package MyBookShowroom
 * @since   1.1.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_Classification_Seeder {

	const OPTION_KEY    = 'mbs_classification_db_version';
	const BATCH_SIZE    = 100;
	const NOTICE_KEY    = 'mbs_classification_seeded_notice';

	/**
	 * Bump this constant (not MBS_VERSION) whenever a bundled JSON data file
	 * changes. Decoupling ensures maybe_seed() re-runs even when the plugin
	 * version itself has not changed (EXP-006 fix).
	 *
	 * History:
	 *   1.0.0 -- initial seed data
	 *   1.0.1 -- bic-thema-map.json stale-row correction; BIC description
	 *            normalisation (EXP-006 / EXP-007, Session 131)
	 */
	const DATA_VERSION  = '1.0.1';

	/**
	 * Register the admin_init hook for classification seeding and the
	 * admin_notices hook for the post-seed notice.
	 * Moved from MBS_Plugin::load_data_layer() — REFACTOR-PLAN-ARCH-001 Batch C.
	 */
	public static function register_hooks(): void {
		add_action( 'admin_notices', array( __CLASS__, 'maybe_show_seeded_notice' ) );
	}

	/**
	 * Run the seeder only when the stored version is stale or absent.
	 *
	 * @return void
	 */
	public static function maybe_seed(): void {
		if ( get_option( self::OPTION_KEY ) === self::DATA_VERSION ) {
			return;
		}
		self::seed();
	}

	/**
	 * (Re-)create tables and repopulate from bundled JSON data files.
	 *
	 * Idempotent: safe to call multiple times. Existing rows are deleted
	 * before re-insertion so the data always reflects the bundled files.
	 *
	 * @return void
	 */
	public static function seed(): void {
		global $wpdb;
		$cc = $wpdb->get_charset_collate();

		$codes_table   = $wpdb->prefix . 'mbs_classification_codes';
		$map_table     = $wpdb->prefix . 'mbs_bisac_thema_map';
		$bic_map_table = $wpdb->prefix . 'mbs_bic_thema_map';

		// Create tables with raw SQL -- dbDelta is unreliable on Windows/XAMPP and has
		// silently failed to create these tables in multiple sessions. Raw CREATE TABLE
		// IF NOT EXISTS is verified to work on this environment (Session 85 diagnostic).
  // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- DAO layer -- direct DB access is intentional per MBS architecture
		$wpdb->query( "CREATE TABLE IF NOT EXISTS `{$codes_table}` (
			id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
			scheme      VARCHAR(10)  NOT NULL,
			code        VARCHAR(30)  NOT NULL,
			description VARCHAR(512) NOT NULL DEFAULT '',
			notes       TEXT         NOT NULL,
			parent_code VARCHAR(30)  NOT NULL DEFAULT '',
			PRIMARY KEY (id),
			UNIQUE KEY scheme_code (scheme, code),
			KEY scheme_idx (scheme),
			KEY parent_idx (parent_code)
		) {$cc}" );
  // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- DAO layer -- direct DB access is intentional per MBS architecture
		$wpdb->query( "CREATE TABLE IF NOT EXISTS `{$map_table}` (
			id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
			bisac_code   VARCHAR(30)  NOT NULL,
			thema_code   VARCHAR(30)  NOT NULL,
			is_qualifier TINYINT(1)   NOT NULL DEFAULT 0,
			PRIMARY KEY (id),
			KEY bisac_idx (bisac_code),
			KEY thema_idx (thema_code)
		) {$cc}" );
  // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- DAO layer -- direct DB access is intentional per MBS architecture
		$wpdb->query( "CREATE TABLE IF NOT EXISTS `{$bic_map_table}` (
			id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
			bic_code     VARCHAR(30)  NOT NULL,
			thema_code   VARCHAR(30)  NOT NULL,
			is_qualifier TINYINT(1)   NOT NULL DEFAULT 0,
			PRIMARY KEY (id),
			KEY bic_idx   (bic_code),
			KEY thema_idx (thema_code)
		) {$cc}" );

		// Normalise collation on all tables to match this WP install.
		// CREATE TABLE IF NOT EXISTS preserves the original collation when a table
		// already exists, which causes collation-mismatch errors on JOINs if the
		// tables were created at different times. CONVERT TO CHARACTER SET
		// re-encodes all string columns consistently before every seed cycle.
		preg_match( '/COLLATE\s+(\S+)/', $cc, $cm );
		$collate = $cm[1] ?? 'utf8mb4_unicode_ci';
		$wpdb->query( "ALTER TABLE `{$codes_table}` CONVERT TO CHARACTER SET utf8mb4 COLLATE {$collate}" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.NotPrepared -- DAO layer -- direct DB access is intentional per MBS architecture
		$wpdb->query( "ALTER TABLE `{$map_table}` CONVERT TO CHARACTER SET utf8mb4 COLLATE {$collate}" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.NotPrepared -- DAO layer -- direct DB access is intentional per MBS architecture
		$wpdb->query( "ALTER TABLE `{$bic_map_table}` CONVERT TO CHARACTER SET utf8mb4 COLLATE {$collate}" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.NotPrepared -- DAO layer -- direct DB access is intentional per MBS architecture

		// Clear existing rows before re-seeding.
		$wpdb->query( "DELETE FROM {$codes_table}" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- DAO layer -- direct DB access is intentional per MBS architecture
		$wpdb->query( "DELETE FROM {$map_table}" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- DAO layer -- direct DB access is intentional per MBS architecture
		$wpdb->query( "DELETE FROM {$bic_map_table}" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- DAO layer -- direct DB access is intentional per MBS architecture

		// Seed each table from bundled JSON.
		$bisac_count   = self::seed_bisac_codes( $codes_table );
		$thema_count   = self::seed_thema_codes( $codes_table );
		$bowker_count  = self::seed_bowker_codes( $codes_table );
		$bic_count     = self::seed_bic_codes( $codes_table );
		self::seed_map( $map_table );
		self::seed_bic_thema_map( $bic_map_table );

		// Only mark version as seeded when BISAC rows were actually inserted.
		// If bisac_count is 0 (JSON missing or DB error), leave the option
		// stale so maybe_seed() retries on the next page load.
		if ( $bisac_count > 0 ) {
			update_option( self::OPTION_KEY, self::DATA_VERSION );
			set_transient(
				self::NOTICE_KEY,
				array(
					'bisac'   => $bisac_count,
					'thema'   => $thema_count,
					'bowker'  => $bowker_count,
					'bic'     => $bic_count,
				),
				HOUR_IN_SECONDS
			);
		}
	}

	/**
	 * Returns the table names used by this seeder and the DAO.
	 *
	 * @return array{ codes: string, map: string, bic_map: string }
	 */
	public static function get_table_names(): array {
		global $wpdb;
		return array(
			'codes'   => $wpdb->prefix . 'mbs_classification_codes',
			'map'     => $wpdb->prefix . 'mbs_bisac_thema_map',
			'bic_map' => $wpdb->prefix . 'mbs_bic_thema_map',
		);
	}

	/**
	 * Display the one-time seeded notice for admins.
	 *
	 * Hook onto admin_notices.
	 *
	 * @return void
	 */
	public static function maybe_show_seeded_notice(): void {
		$data = get_transient( self::NOTICE_KEY );
		if ( ! $data || ! current_user_can( 'manage_options' ) ) {
			return;
		}
		delete_transient( self::NOTICE_KEY );
		?>
		<div class="notice notice-success is-dismissible">
			<p><?php echo esc_html( sprintf(
				/* translators: 1: BISAC count 2: Thema count 3: Bowker count 4: BIC count */
				__( 'My Book Showroom: Classification data loaded -- %1$d BISAC, %2$d Thema, %3$d Bowker, %4$d BIC codes.', 'my-book-showroom' ),
				(int) $data['bisac'],
				(int) $data['thema'],
				(int) ( $data['bowker'] ?? 0 ),
				(int) ( $data['bic'] ?? 0 )
			) ); ?></p>
		</div>
		<?php
	}

	// -------------------------------------------------------------------------
	// Schema
	// -------------------------------------------------------------------------

	/**
	 * CREATE TABLE DDL for mbs_classification_codes.
	 *
	 * Stores all BISAC codes and all Thema codes (subject + qualifiers) for
	 * Layer 2 typeahead search. The notes column holds Thema CodeNotes;
	 * it is an empty string for BISAC rows.
	 *
	 * @param string $table           Fully-qualified table name.
	 * @param string $charset_collate Output of $wpdb->get_charset_collate().
	 * @return string
	 */
	private static function codes_schema( string $table, string $charset_collate ): string {
		return "CREATE TABLE {$table} (
			id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
			scheme      VARCHAR(10)  NOT NULL,
			code        VARCHAR(30)  NOT NULL,
			description VARCHAR(512) NOT NULL DEFAULT \'\',
			notes       TEXT         NOT NULL,
			parent_code VARCHAR(30)  NOT NULL DEFAULT \'\',
			PRIMARY KEY (id),
			UNIQUE KEY scheme_code (scheme, code),
			KEY scheme (scheme),
			KEY parent_code (parent_code)
		) {$charset_collate};";
	}

	/**
	 * CREATE TABLE DDL for mbs_bisac_thema_map.
	 *
	 * One row per BISAC-Thema pair. is_qualifier distinguishes subject codes
	 * (0) from place/language/style qualifiers (1).
	 *
	 * @param string $table           Fully-qualified table name.
	 * @param string $charset_collate Output of $wpdb->get_charset_collate().
	 * @return string
	 */
	private static function map_schema( string $table, string $charset_collate ): string {
		return "CREATE TABLE {$table} (
			id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
			bisac_code   VARCHAR(30)  NOT NULL,
			thema_code   VARCHAR(30)  NOT NULL,
			is_qualifier TINYINT(1)   NOT NULL DEFAULT 0,
			PRIMARY KEY (id),
			KEY bisac_code (bisac_code),
			KEY thema_code (thema_code)
		) {$charset_collate};";
	}

	// -------------------------------------------------------------------------
	// Seeders
	// -------------------------------------------------------------------------

	/**
	 * Seed all unique BISAC codes into mbs_classification_codes.
	 *
	 * Descriptions are sourced from bisac-codes.json (bundled data file).
	 * Codes and descriptions are read directly from that JSON source;
	 * MBS_BISAC_Codes is not consulted during seeding.
	 *
	 * @param string $table Fully-qualified table name.
	 * @return int Number of rows inserted.
	 */
	private static function seed_bisac_codes( string $table ): int {
		global $wpdb;

		// Primary source: bundled bisac-codes.json (5,570 codes with full descriptions
		// scraped from bisg.org). Fallback: bisac-thema-map.json unique codes (no desc).
		$codes_path = MBS_DIR . 'data/bisac-codes.json';
		if ( file_exists( $codes_path ) ) {
			$codes_data = json_decode( (string) file_get_contents( $codes_path ), true );
		} else {
			$codes_data = array();
		}

		// Build description lookup from bisac-codes.json.
		$descriptions = array();
		foreach ( $codes_data as $entry ) {
			if ( isset( $entry['code'], $entry['description'] ) ) {
				$descriptions[ $entry['code'] ] = $entry['description'];
			}
		}

		// Also include any codes in the mapping that have no description entry.
		$map_path = MBS_DIR . 'data/bisac-thema-map.json';
		$map_data = json_decode( (string) file_get_contents( $map_path ), true );
		$unique   = array();
		foreach ( $map_data as $row ) {
			$unique[ $row['bisac_code'] ] = true;
		}
		// Merge: codes from json take precedence; add map-only codes with empty desc.
		foreach ( array_keys( $unique ) as $code ) {
			if ( ! isset( $descriptions[ $code ] ) ) {
				$descriptions[ $code ] = '';
			}
		}

		$rows  = array();
		$count = 0;
		foreach ( $descriptions as $code => $desc ) {
			$rows[] = $wpdb->prepare( '(%s, %s, %s, %s, %s)', 'bisac', $code, $desc, '', '' );
			if ( count( $rows ) >= self::BATCH_SIZE ) {
				$wpdb->query( "INSERT INTO {$table} (scheme, code, description, notes, parent_code) VALUES " . implode( ',', $rows ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- DAO layer -- direct DB access is intentional per MBS architecture
				$count += (int) $wpdb->rows_affected;
				$rows   = array();
			}
		}
		if ( $rows ) {
			$wpdb->query( "INSERT INTO {$table} (scheme, code, description, notes, parent_code) VALUES " . implode( ',', $rows ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- DAO layer -- direct DB access is intentional per MBS architecture
			$count += (int) $wpdb->rows_affected;
		}
		return $count;
	}

	/**
	 * Seed all Thema codes (subject + qualifier) into mbs_classification_codes.
	 *
	 * @param string $table Fully-qualified table name.
	 * @return int Number of rows inserted.
	 */
	private static function seed_thema_codes( string $table ): int {
		global $wpdb;

		$codes_path = MBS_DIR . 'data/thema-codes.json';
		$codes      = json_decode( (string) file_get_contents( $codes_path ), true );

		$rows  = array();
		$count = 0;
		foreach ( $codes as $c ) {
			$rows[] = $wpdb->prepare(
				'(%s, %s, %s, %s, %s)',
				'thema',
				$c['code'],
				$c['description'],
				$c['notes'],
				$c['parent']
			);
			if ( count( $rows ) >= self::BATCH_SIZE ) {
				$wpdb->query( "INSERT INTO {$table} (scheme, code, description, notes, parent_code) VALUES " . implode( ',', $rows ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- DAO layer -- direct DB access is intentional per MBS architecture
				$count += (int) $wpdb->rows_affected;
				$rows   = array();
			}
		}
		if ( $rows ) {
			$wpdb->query( "INSERT INTO {$table} (scheme, code, description, notes, parent_code) VALUES " . implode( ',', $rows ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- DAO layer -- direct DB access is intentional per MBS architecture
			$count += (int) $wpdb->rows_affected;
		}
		return $count;
	}

	/**
	 * Seed Bowker Subject Codes into mbs_classification_codes (scheme='bowker').
	 *
	 * Source: data/bowker-codes.json -- 90 codes compiled from the Bowker
	 * ISBN Agency subject list. Each entry has 'code' and 'description'.
	 *
	 * @param string $table Fully-qualified table name.
	 * @return int Number of rows inserted.
	 */
	private static function seed_bowker_codes( string $table ): int {
		global $wpdb;

		$codes_path = MBS_DIR . 'data/bowker-codes.json';
		if ( ! file_exists( $codes_path ) ) {
			return 0;
		}
		$codes = json_decode( (string) file_get_contents( $codes_path ), true );
		if ( ! is_array( $codes ) ) {
			return 0;
		}

		$rows  = array();
		$count = 0;
		foreach ( $codes as $c ) {
			if ( ! isset( $c['code'], $c['description'] ) ) {
				continue;
			}
			$rows[] = $wpdb->prepare( '(%s, %s, %s, %s, %s)', 'bowker', $c['code'], $c['description'], '', '' );
			if ( count( $rows ) >= self::BATCH_SIZE ) {
				$wpdb->query( "INSERT INTO {$table} (scheme, code, description, notes, parent_code) VALUES " . implode( ',', $rows ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- DAO layer -- direct DB access is intentional per MBS architecture
				$count += (int) $wpdb->rows_affected;
				$rows   = array();
			}
		}
		if ( $rows ) {
			$wpdb->query( "INSERT INTO {$table} (scheme, code, description, notes, parent_code) VALUES " . implode( ',', $rows ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- DAO layer -- direct DB access is intentional per MBS architecture
			$count += (int) $wpdb->rows_affected;
		}
		return $count;
	}

	/**
	 * Seed BIC v2.1 Subject Category codes into mbs_classification_codes (scheme='bic').
	 *
	 * Source: data/bic-codes.json -- 2,688 codes extracted from the BIC v2.1
	 * complete category list (November 2010, frozen). Each entry has 'code'
	 * and 'description'.
	 *
	 * @param string $table Fully-qualified table name.
	 * @return int Number of rows inserted.
	 */
	private static function seed_bic_codes( string $table ): int {
		global $wpdb;

		$codes_path = MBS_DIR . 'data/bic-codes.json';
		if ( ! file_exists( $codes_path ) ) {
			return 0;
		}
		$codes = json_decode( (string) file_get_contents( $codes_path ), true );
		if ( ! is_array( $codes ) ) {
			return 0;
		}

		// EXP-007: BIC parent-level codes (1-2 chars) are stored ALL CAPS in the
		// source data. Normalise every description to title case for display
		// consistency with BISAC and Thema. A small override table restores known
		// acronyms and proper names that ucwords(strtolower()) would mangle.
		$bic_desc_overrides = array(
			'ULN'   => 'UNIX',
			'ULLS'  => 'SUSE Linux',
			'ULLU'  => 'Ubuntu',
			'ULNH'  => 'HP-UX',
			'ULNM'  => 'IBM AIX',
			'UNSX'  => 'IBM DB2',
			'MJCJ2' => 'HIV / AIDS',
		);

		$rows  = array();
		$count = 0;
		foreach ( $codes as $c ) {
			if ( ! isset( $c['code'], $c['description'] ) ) {
				continue;
			}
			// Normalise description: title-case, then restore any known acronym.
			$desc = ucwords( strtolower( $c['description'] ) );
			if ( isset( $bic_desc_overrides[ $c['code'] ] ) ) {
				$desc = $bic_desc_overrides[ $c['code'] ];
			}
			$rows[] = $wpdb->prepare( '(%s, %s, %s, %s, %s)', 'bic', $c['code'], $desc, '', '' );
			if ( count( $rows ) >= self::BATCH_SIZE ) {
				$wpdb->query( "INSERT INTO {$table} (scheme, code, description, notes, parent_code) VALUES " . implode( ',', $rows ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- DAO layer -- direct DB access is intentional per MBS architecture
				$count += (int) $wpdb->rows_affected;
				$rows   = array();
			}
		}
		if ( $rows ) {
			$wpdb->query( "INSERT INTO {$table} (scheme, code, description, notes, parent_code) VALUES " . implode( ',', $rows ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- DAO layer -- direct DB access is intentional per MBS architecture
			$count += (int) $wpdb->rows_affected;
		}
		return $count;
	}

	/**
	 * Seed the BISAC-Thema mapping rows into mbs_bisac_thema_map.
	 *
	 * @param string $table Fully-qualified table name.
	 * @return int Number of rows inserted.
	 */
	private static function seed_map( string $table ): int {
		global $wpdb;

		$map_path = MBS_DIR . 'data/bisac-thema-map.json';
		$map_data = json_decode( (string) file_get_contents( $map_path ), true );

		$rows  = array();
		$count = 0;
		foreach ( $map_data as $row ) {
			$rows[] = $wpdb->prepare(
				'(%s, %s, %d)',
				$row['bisac_code'],
				$row['thema_code'],
				(int) $row['is_qualifier']
			);
			if ( count( $rows ) >= self::BATCH_SIZE ) {
				$wpdb->query( "INSERT INTO {$table} (bisac_code, thema_code, is_qualifier) VALUES " . implode( ',', $rows ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- DAO layer -- direct DB access is intentional per MBS architecture
				$count += (int) $wpdb->rows_affected;
				$rows   = array();
			}
		}
		if ( $rows ) {
			$wpdb->query( "INSERT INTO {$table} (bisac_code, thema_code, is_qualifier) VALUES " . implode( ',', $rows ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- DAO layer -- direct DB access is intentional per MBS architecture
			$count += (int) $wpdb->rows_affected;
		}
		return $count;
	}

	/**
	 * Seed the BIC-Thema mapping rows into mbs_bic_thema_map.
	 *
	 * Source: data/bic-thema-map.json -- 3,958 rows derived from the EDItEUR
	 * BIC 2.1 to Thema 1.6 mapping (unconditional subject rows + all qualifier
	 * rows). is_qualifier=0 for subject mappings, is_qualifier=1 for qualifiers.
	 *
	 * @param string $table Fully-qualified table name.
	 * @return int Number of rows inserted.
	 */
	private static function seed_bic_thema_map( string $table ): int {
		global $wpdb;

		$map_path = MBS_DIR . 'data/bic-thema-map.json';
		if ( ! file_exists( $map_path ) ) {
			return 0;
		}
		$map_data = json_decode( (string) file_get_contents( $map_path ), true );
		if ( ! is_array( $map_data ) ) {
			return 0;
		}

		$rows  = array();
		$count = 0;
		foreach ( $map_data as $row ) {
			$rows[] = $wpdb->prepare(
				'(%s, %s, %d)',
				$row['bic_code'],
				$row['thema_code'],
				(int) $row['is_qualifier']
			);
			if ( count( $rows ) >= self::BATCH_SIZE ) {
				$wpdb->query( "INSERT INTO {$table} (bic_code, thema_code, is_qualifier) VALUES " . implode( ',', $rows ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- DAO layer -- direct DB access is intentional per MBS architecture
				$count += (int) $wpdb->rows_affected;
				$rows   = array();
			}
		}
		if ( $rows ) {
			$wpdb->query( "INSERT INTO {$table} (bic_code, thema_code, is_qualifier) VALUES " . implode( ',', $rows ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- DAO layer -- direct DB access is intentional per MBS architecture
			$count += (int) $wpdb->rows_affected;
		}
		return $count;
	}
}
