<?php
/**
 * File Path: includes/migrations/class-mbs-migration-4-29.php
 *
 * Migration 4.29 -- Session 185: Reading Order Junction Table (R2-002)
 *
 * Creates mbs_reading_order (id, term_id, post_id, sort_order, type) and
 * migrates any existing reading order data stored as JSON in term_meta for
 * series, collection, and universe taxonomies.
 *
 * Idempotency: dbDelta only creates the table if absent. The per-pair
 * existence check prevents duplicate rows if the migration is re-run.
 *
 * @package MyBookShowroom
 * @since   1.5.0
 */

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

/**
 * Migration 4.29: Create mbs_reading_order and migrate JSON term_meta data.
 *
 * @implements MBS_Migration
 */
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- migration: table names via $wpdb->prefix are trusted server-controlled values
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter -- same reason: $wpdb->prefix table names are safe
class MBS_Migration_4_29 implements MBS_Migration { // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedClassFound -- mbs_/MBS_ prefix is correct; scanner not configured with plugin prefix

	/**
	 * Return the migration version string.
	 *
	 * @return string
	 * @since 1.5.0
	 */
	public function version(): string {
		return '4.29';
	}

	/**
	 * Run the migration.
	 *
	 * 1. Creates mbs_reading_order via dbDelta (idempotent).
	 * 2. Reads each term that has a reading order stored as JSON in term_meta
	 *    for the series, collection, and universe taxonomy types.
	 * 3. Inserts one row per (term_id, post_id, type) into mbs_reading_order,
	 *    skipping pairs that already exist.
	 *
	 * @return void
	 * @since 1.5.0
	 */
	public function up(): void {
		global $wpdb;
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$ro_table        = $wpdb->prefix . 'mbs_reading_order';
		$charset_collate = $wpdb->get_charset_collate();

		// Step 1: Create the junction table.
		dbDelta( MBS_DB_Schema::reading_order( $ro_table, $charset_collate ) );

		// Step 2: Migrate JSON term_meta for each taxonomy type.
		$sources = array(
			'series'     => 'mbs_series_reading_order',
			'collection' => 'mbs_collection_reading_order',
			'universe'   => 'mbs_universe_reading_order',
		);

		foreach ( $sources as $type => $meta_key ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name from $wpdb->prefix
			$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
				$wpdb->prepare(
					"SELECT term_id, meta_value FROM {$wpdb->termmeta}
					 WHERE meta_key = %s AND meta_value IS NOT NULL",
					$meta_key
				)
			);

			if ( empty( $rows ) ) {
				continue;
			}

			foreach ( $rows as $row ) {
				$term_id  = (int) $row->term_id;
				$post_ids = json_decode( (string) $row->meta_value, true );

				if ( ! is_array( $post_ids ) || empty( $post_ids ) ) {
					continue;
				}

				$sort = 0;
				foreach ( $post_ids as $post_id ) {
					$post_id = (int) $post_id;
					if ( $post_id <= 0 ) {
						$sort++;
						continue;
					}

					// Idempotency: skip if this (term_id, post_id, type) already exists.
					$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(
							// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
							"SELECT COUNT(*) FROM {$ro_table}
							 WHERE term_id = %d AND post_id = %d AND type = %s",
							$term_id,
							$post_id,
							$type
						)
					);

					if ( 0 === $exists ) {
						$wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
							$ro_table,
							array(
								'term_id'    => $term_id,
								'post_id'    => $post_id,
								'sort_order' => $sort,
								'type'       => $type,
							),
							array( '%d', '%d', '%d', '%s' )
						);
					}
					$sort++;
				}
			}
		}
	}
}
